Date Filter (XlDynamicFilterCriteria) with multiple criteria

user_9121235 0
Viewed 190

I am trying to filter a column with 2 criteria. I need all data from this month and last month.

what is working: one single criteria, the Operator is "xlFilterDynamic"

Sub filtertest()
    Dim LastMonth As XlDynamicFilterCriteria
    LastMonth = xlFilterLastMonth
    
    Dim ThisMonth As XlDynamicFilterCriteria
    ThisMonth = xlFilterThisMonth
    
    ActiveSheet.Range("A1:B92").AutoFilter Field:=1, Criteria1:=ThisMonth, Operator:=xlFilterDynamic
End Sub

Result: enter image description here

What is not working: two criteria, the Operator is still "xlFilterDynamic", but maybe that's not correct. I put the two criteria into an array. I receive an error 1004.

Sub filtertest_withArray()

    Dim LastMonth As XlDynamicFilterCriteria
    LastMonth = xlFilterLastMonth
    
    Dim ThisMonth As XlDynamicFilterCriteria
    ThisMonth = xlFilterThisMonth
    
    Dim arrCriteria() As Variant
    ReDim arrCriteria(0 To 1)
    arrCriteria(0) = LastMonth
    arrCriteria(1) = ThisMonth
    
    ActiveSheet.Range("A1:B92").AutoFilter Field:=1, Criteria1:=arrCriteria(), Operator:=xlFilterDynamic
End Sub

But if I set the Operator to "xlFilterValues" it runs through without an error, but all datasets are filtered out.

enter image description here

edit: When I set the filter to this, it won't work either. It will just filter the dates from last month.

Criteria1:=LastMonth, Operator:=xlFilterDynamic, Criteria2:=ThisMonth, Operator:=xlFilterDynamic
vba excel
2 Answers
Sub FilterLastAndCurrentMonths()
    Dim lastDayOfCurrentMonth As Variant
    Dim lastDayOfLastMonth As Variant
    
    With Application
        lastDayOfCurrentMonth = Format(.WorksheetFunction.EoMonth(Date, 0), "mm/dd/yyyy")
        lastDayOfLastMonth = Format(.WorksheetFunction.EoMonth(Date, -1), "mm/dd/yyyy")
    End With

    ActiveSheet.Range("$D$3:$D$50").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, lastDayOfLastMonth, 1, lastDayOfCurrentMonth)
        
End Sub

enter image description here

user_68144650

I don't think you need dynamic criteria to achieve what you are (were) seeking to achieve.

Searching between two date criteria works in below example using AND operator.

'Establish criteria bounds
Dim startDate As Date, endDate As Date
startDate = DateSerial(Year(Date), Month(Date) - 1, 1)
endDate = DateSerial(Year(Date), Month(Date) + 1, 0)

'Clear filter
ActiveSheet.Range("A1:B92").AutoFilter Field:=1

'Set Filter
ActiveSheet.Range("A1:B92").AutoFilter Field:=1, Criteria1:= _
    ">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate

A great way to play around with table filtering is to record a macro, set filters as you'd like manually, and then review the recorded code.

user_14128630
Related