Solved

Autofilter Today and Yesterdays Dates.

Posted on 2014-02-28
9
446 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 32

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 32

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

914 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

16 Experts available now in Live!

Get 1:1 Help Now