Excel VBA AutoFilter Error Filtering with Date Range

Question

My goal is to filter a range of rows by a date in column A. I am using parameter Criteria1 to specify the first operator & date, Criteria2 for the second operator and date, and "xlAnd" for the Operator parameter.

The range of dates to filter are in rows 2 to 1255 of column A...

Range("A2:A1255").AutoFilter Field:=1, Criteria1:=">10/1/2022", Operator:="xlAnd", Criteria2:="<12/1/2022"

However, the following error is raised when this code is attempted:

Error 1004:  AutoFilter method of Range class failed

As a workaround, I tried two consecutive AutoFilters, for each criteria. Unfortunately, the second filter did not filter on the output from the first filter, but also on the range as a whole.

I am at a loss. Thanks in advance.

Answer 1

Please try to specify the date as below and xlAnd is a const without quotation.

    ActiveSheet.Range("$A$2:$A$1255").AutoFilter Field:=1, Criteria1:= _
        ">2022/10/1", Operator:=xlAnd, Criteria2:="<2022/12/1"