Autofilter Today and Yesterdays Dates.

RWayneH
RWayneH used Ask the Experts™
on
I am currently using the following code to filter column 16 by the current date.  What I would like to do is filter by today and yesterday.  How do I rewrite this to do this and what is the =Array(0,   do?  I would like to be able to write these...

Like filter by today, yesterday, and the day before that. (have three days)
Another tough one for me is, filter by today and the fourth day back (2 days)

ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
    xlFilterValues, Criteria2:=Array(0, DateValue(Now)) 

Open in new window


I am having some issues understanding whether i should use NOW() or if I should grab what the current day of the week is?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
Doing a Filter manually for dates between 28/02 and 26/02 I recorded the following:

Sub Macro2()

ActiveSheet.Range("$B$1:$B$23").AutoFilter Field:=1, Criteria1:= _
        ">=26/02/2014", Operator:=xlAnd, Criteria2:="<=28/02/2014"

End Sub

Open in new window

Converting the dates to be calculated, I came up with:

Sub Filter()
    StartDate = Date
    FinishDate = Date - 2
    ActiveSheet.Range("$B$1:$B$23").AutoFilter Field:=1, _
    Criteria1:="<=" & StartDate, _
    Operator:=xlAnd, _
    Criteria2:=">=" & FinishDate
End Sub

Open in new window


However, when I run the code it doesn't work???

I have a feeling the Filter is not recognising dates from some bizarre reason.

Thanks
Rob H
Try

ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
    xlFilterValues, Criteria2:=Array(0, DateValue(Now), DateValue(Now-1))
Finance Analyst
Commented:
To include today, yesterday and the day before:

Date1 = Format(Date - 2, "mm/dd/yyyy")
Date2 = Format(Date - 1, "mm/dd/yyyy")
Date3 = Format(Date, "mm/dd/yyyy")
    ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Date1, 2, Date2, 2, Date3)
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Rob, are you sure you wanted to repeat the twos in the array?

Author

Commented:
That was one of my questions, what is the =Array(2,    what does it do?  I have had some 0, others with 2?  I like the way this is layed out, this way I can put in any number of dates into the filter..
When you click the dropdown for the filter you get a number of selectable items.

You can check/uncheck any number of them.

The array items is a list of all the checked items.

Author

Commented:
So does this screw things up if I set 3 dates to filter by and only two are there?  So if I am selecting 3 does it have to be 3?  Is that Array number needed as a prefix in the dates?
(2, Date1, 2, Date2, 2, Date3)?  or should this be a 3? or is it even needed?
If there is more information on this... it would be helpful.
You would only need to list your requirement. No number is needed.

Author

Commented:
Thank you both for helping me understand this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial