excelvbaautofilter

3 criteria for Autofilter Macro, error: Expect Expression problem solving

I've checked so far and there are no question on 3 autofilter criteria with the error message of expression not defined.

Context: I'm making a vba of data range with 3 different set of data: Today, tomorrow and the day after tomorrow.

Previously, the code that I used for 2 criteria does not have any issue. But when I add a new criteria, I found a new error. Attached is my code and kindly let me know what can I improve and solve for this.

    Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim TodaysDate As Date
    Dim TomorrowsDate As Date
    
    '~~> Today's date
    TodaysDate = Date
    '~~> Tomorrow's date
    TomorrowsDate = DateAdd("d", 1, Date)
    '~~> After's Date
    Aftersdate = DateAdd("d", 2, Date)
    
    '~~> Set this to the relevant worksheet
    Set ws = Worksheets("STG_SB_OPICS_DTL")
    
    With ws
        '~~> Remove any existing filter
        .AutoFilterMode = False
        
        '~~> Find last row in Col D
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify your filter range
        Set rng = .Range("D3:D" & lRow)
        
        '~~> Filter
        With rng
            .AutoFilter field:=1, _
            Criteria1:="=" & TodaysDate, _
            Operator:=xlOr, _
            Criteria2:="=" & TomorrowsDate
            Criteria3:="=" & Aftersdate
        End With
    End With
End Sub
Solution

  • Filter On Multiple Criteria

    enter image description here

    Option Explicit ' Use it at the top of each module...

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim TodaysDate As Date
    Dim TomorrowsDate As Date
    Dim AftersDate As Date ' ... to get alerted when a variable is not declared.
    
    '~~> Today's date
    TodaysDate = Date
    '~~> Tomorrow's date
    TomorrowsDate = DateAdd("d", 1, Date)
    '~~> After's Date
    AftersDate = DateAdd("d", 2, Date)
    
    '~~> Set this to the relevant worksheet
    Set ws = Worksheets("STG_SB_OPICS_DTL")
    
    With ws
        '~~> Remove any existing filter
        .AutoFilterMode = False
        
        '~~> Find last row in Col D
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify your filter range
        Set rng = .Range("D3:D" & lRow)
        
   End With
        
    ' Return the dates in an array.
    Dim DatesArray() As Variant:
    DatesArray = Array(TodaysDate, TomorrowsDate, AftersDate)
    
    ' Filter using the array with 'xlFilterValues'.
    With rng
        .AutoFilter Field:=1, _
        Criteria1:=DatesArray, _
        Operator:=xlFilterValues
    End With

End Sub