Results 1 to 3 of 3

Thread: Autofilters

  1. #1

    Autofilters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys

    i'm using the following to autofilter a field by month
    Code:
    Worksheets("Master").Range("$A:$AB").AutoFilter Field:=5, Criteria1:=xlFilterAllDatesInPeriodJuly, Operator:=xlFilterDynamic
    this works perfect if all the data in the master sheet is from the same year

    what I need to do is specify the month and the year so that it only returns records that are from a specific month and a specific year

    The field number 5 has a date and time in it in the following format

    "03/07/2013 10:47:56"

    i need to set a variable as follows

    Code:
    reportmonth = "7" ;EG July
    reportyear = "2013"
    I cant for the life of me work out how to specify the two criteria and return the correct number of records

    any ideas

    mark
    Last edited by NBVC; 2013-08-26 at 01:21 PM. Reason: added code tags

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try recording a macro while manually doing the same filtering.... it should give you an idea.


  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Don't know how you are going about determining what month to filter on but if you have a look here you will be able to have variables for the 1st of the month and the last of the month and then use

    Code:
    Worksheets("Master").Range("$A:$AB").AutoFilter Field:=5, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •