Autofilter Today and Yesterdays Dates.

Posted on 2014-02-28
Last Modified: 2014-02-28
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?
Question by:RWayneH
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 33

Expert Comment

by:Rob Henson
ID: 39895271
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.

Rob H
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39895273

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

Accepted Solution

Rob Henson earned 250 total points
ID: 39895299
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)
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39895316
Rob, are you sure you wanted to repeat the twos in the array?

Author Comment

ID: 39895362
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..
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39895407
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 Comment

ID: 39895445
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.
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 39895460
You would only need to list your requirement. No number is needed.

Author Closing Comment

ID: 39895468
Thank you both for helping me understand this.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question