Help on my filter please

[Eric Penfold]

Please could you help as to why the filter will not work in this code. Need yesterdays & todays filtered.

Sub FindValues()

    Dim ws           As Worksheet
    Dim Rng          As Range
    Dim Lrow         As Long, Yesterday As Long, ToDay As Long
    Dim Comparerng   As Variant, x As Variant, y As Variant, i As Variant
    Dim Sourcerng    As Range
    Dim YDate        As Date
    Dim TDate        As Date
    
    On Error Resume Next
    Set ws = ActiveSheet
    Lrow = ws.Range("A2").End(xlDown).Row
    Set Rng = ws.Range("A2:J" & Lrow)
    
        ToDay = (DateSerial(Year(Date), Month(Date), Day(Date)))
        TDate = ToDay
        TDate = Format(TDate, "mm/dd/yyyy")
        
        Yesterday = (DateSerial(Year(Date), Month(Date), Day(Date - 1)))
        YDate = Yesterday
        YDate = Format(YDate, "mm/dd/yyyy")
        
        Rng.AutoFilter Field:=1, _
        Criteria1:=">" & YDate, _
        Operator:=xlAnd, _
        Criteria2:="<" & TDate

       End Sub

 

[davesexcel]

It could be possible you need >= and <=

Perhaps run the custom filter first using the macro recorder to see how the dates look.

Normally you just need to use date for example

    today = Date
    yesterday = today - 1


    ActiveSheet.Range("$A$1:$A$30").AutoFilter Field:=1, Criteria1:= _
                                               ">=" & yesterday, Operator:=xlAnd, Criteria2:="<=" & today

 

[Eric Penfold]

I`ve tried what you said but the criteria`s says expression not defined in context??

 

[Eric Penfold]

Please could i have some help today on this

 

[kevin9999]

Please try the following

Sub Filter_2_days()
    With ActiveSheet.Range("A2", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "mm/dd/yyyy"), 2, Format(Date - 1, "mm/dd/yyyy")
    End With
End Sub

 

[Eric Penfold]

Please try the following

Sub Filter_2_days()
    With ActiveSheet.Range("A2", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "mm/dd/yyyy"), 2, Format(Date - 1, "mm/dd/yyyy")
    End With
End Sub

I tried this and it just filters the column blank??
The same happened on my previous attempts.

 

[kevin9999]

I tried this and it just filters the column blank??
The same happened on my previous attempts.

What format are the dates currently in? A sample of your data would make life much easier.

 

[Eric Penfold]

 

[Eric Penfold]

I`ve made first column British date format.

 

[kevin9999]

Thanks Eric. Try this now: (EDITED)

Sub Filter_2_days_v2()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy"), 0
    End With
End Sub