[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

Autofilter Today and Yesterdays Dates.

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?
0
RWayneH
Asked:
RWayneH
  • 4
  • 3
  • 2
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Try

ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
    xlFilterValues, Criteria2:=Array(0, DateValue(Now), DateValue(Now-1))
0
 
Rob HensonIT & Database AssistantCommented:
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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Saqib Husain, SyedEngineerCommented:
Rob, are you sure you wanted to repeat the twos in the array?
0
 
RWayneHAuthor 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..
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
 
RWayneHAuthor 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.
0
 
Saqib Husain, SyedEngineerCommented:
You would only need to list your requirement. No number is needed.
0
 
RWayneHAuthor Commented:
Thank you both for helping me understand this.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now