Solved

Autofilter Today and Yesterdays Dates.

Posted on 2014-02-28
9
440 Views
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?
0
Comment
Question by:RWayneH
  • 4
  • 3
  • 2
9 Comments
 
LVL 31

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.

Thanks
Rob H
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39895273
Try

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

Accepted Solution

by:
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)
0
 
LVL 43

Expert Comment

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

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:RWayneH
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..
0
 
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.
0
 

Author Comment

by:RWayneH
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.
0
 
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.
0
 

Author Closing Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now