vba code to filter for current date,yesterday date and day before yesterday date

HI,

i am trying  to filter for current date,yesterday date and day before yesterday date with the help of vba code, but it is not working

Date1 = Format(Date - 2, "dd/mm/yyyy")
    Date2 = Format(Date - 1, "dd/mm/yyyy")
    Date3 = Format(Date, "dd/mm/yyyy")
    ActiveSheet.Range("$A:$M").AutoFilter Field:=5, Operator:= _
        xlFilterValues, Criteria2:=Array(2, Date1, 2, Date2, 2, Date3)

please suggest.
rdy123 rdyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
It would be difficult to comment without seeing your workbook or your complete code.

Give this a try, assuming your data starts from row 1, change the RngFilter range accordingly.
Sub MultipleFilter()
Dim Ws As Worksheet
Dim LRow As Long
Dim j As Long
Dim Crit(2) As String
Dim StrIF As String
Dim RngFilter As Range
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Filtering Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
Set Ws = ActiveSheet
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Crit(0) = Format(Date - 2, "dd/mm/yyyy")
Crit(1) = Format(Date - 1, "dd/mm/yyyy")
Crit(2) = Format(Date, "dd/mm/yyyy")
With Ws
    .AutoFilterMode = False
    .Range("N1").Value = "Temp"
    For j = 2 To LRow
        StrIF = "=if(or(isnumber(search(" & Chr(34) & Crit(0) & Chr(34) & ",e" & j & ")),isnumber(search(" & Chr(34) & Crit(1) & Chr(34) _
            & ",e" & j & ")),isnumber(search(" & Chr(34) & Crit(2) & Chr(34) & ",e" & j & "))),999,0)"
        .Range("N" & j).Formula = StrIF
    Next j
End With
Set RngFilter = Ws.Range("A1:N" & LRow)
With RngFilter
    .AutoFilter Field:=14, Criteria1:="0", Operator:=xlFilterValues
End With
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
While AutoFiltering Dates, mention the date criteria in international date format which is yyyy-mm-dd.
See if the following code works for you.

Also, declare the variables properly.

Dim Date1 As String, Date2 As String, Date3 As String

Date1 = Format(Date - 2, "yyyy-mm-dd")
Date2 = Format(Date - 1, "yyyy-mm-dd")
Date3 = Format(Date, "yyyy-mm-dd")

ActiveSheet.AutoFilterMode = False
    
ActiveSheet.Range("$A:$M").autofilter Field:=5, Operator:= _
    xlFilterValues, Criteria2:=Array(2, Date1, 2, Date2, 2, Date3)

Open in new window

1
Roy CoxGroup Finance ManagerCommented:
Filtering with dates is complicated. If you have dates entered as dates in Excel then they are actually a serial number.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi Please find my solution below hope this will run with out any issues,

My suggestion is convert the date into number value and apply filter, once filter applied change back to date format, below coding for reference

Thanks!


Dim Date1 As String, Date2 As String, Date3 As String

Columns(5).EntireColumn.NumberFormat = "0" 




Date1 = Format(Date - 2, "0")
Date2 = Format(Date - 1, "0")
Date3 = Format(Date, "0")

   
ActiveSheet.Range("$A:$M").AutoFilter Field:=5, Operator:= _
    xlFilterValues, Criteria1:=Array(1, Date1, 1, Date2, 1, Date3)
Columns(5).EntireColumn.NumberFormat = "DD/MM/YYYY"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Try this, to filter dates declare then as Dates or Long. This uses a Date Variable and filters for days after today -2

Option Explicit

Sub FilterDates()
Dim dDate As Date

dDate = DateSerial(Year(Date ), Month(Date), Day(Date - 2))
   
ActiveSheet.Range("$A1").CurrentRegion.AutoFilter Field:=5, Operator:= _
    xlFilterValues, Criteria1:=">=" & dDate

End Sub

Open in new window

0
Rob HensonFinance AnalystCommented:
I agree with Roy and was about to post the same suggestion ie "Date >= Today - 2".

Will your dates include future dates? If so you will then need a second criteria of "Date <= Today"
0
Roy CoxGroup Finance ManagerCommented:
Hi Rob

I based my code on the requirements in the first post. The main thing is that the dates need declaring as dates not strings, I thought Chip Pearson had an article on this but I cannot find it, Ozgrid certainly has one
0
rdy123 rdyAuthor Commented:
Thanks all for your inputs:)

will get back back to you all today
1
rdy123 rdyAuthor Commented:
Thank you Both :)
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
ShumsDistinguished Expert - 2017Commented:
Neeraj's solution worked well than accepted solution.
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the confirmation Shums! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.