asked on

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.

Shums Faruk

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

Subodh Tiwari (Neeraj)

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

Roy Cox

Filtering with dates is complicated. If you have dates entered as dates in Excel then they are actually a serial number.

ASKER CERTIFIED SOLUTION

Learn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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"

Roy Cox

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

rdy123 rdy

ASKER

Thanks all for your inputs:)

will get back back to you all today

rdy123 rdy

ASKER

Thank you Both :)

Roy Cox

Pleased to help

Shums Faruk

Neeraj's solution worked well than accepted solution.

Subodh Tiwari (Neeraj)

Thanks for the confirmation Shums! :)