Autofilter for dates error: "Method of Range Class Failed"

1
votes

I have a macro to filter a sales report. It has been working for over a year. It stopped working. Nothing in the report changed.

I am getting an error at the autofilter line. I am trying to filter 3 criteria values in one field (3 dates).

I tried to record a macro filtering the report. When I run it I get

Runtime error: 1004 "AutoFilter method of Range class failed".

Here is the recorded line below, I had changed some of the values in my code to have variables instead of hardcoding but I posted this for testing purpose. I made sure the report is not filtered before trying to autofilter.

ActiveSheet.Range("$A$1:$M$1").AutoFilter Field:=2, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "2/5/2021", 2, "2/6/2021", 2, "2/7/2021")
1
excelvbafilterautofilter

1 Answers

2
votes

This works for me:

ActiveSheet.Range("$A$1:$M$1").AutoFilter Field:=2, Operator:= xlFilterValues, _
  Criteria1:=Array("2/5/2021", "2/6/2021", "2/7/2021")

note Criteria1 not Criteria2

enter image description here

EDIT: FYI I was not aware of this at all but this link https://www.excelcampus.com/vba/filter-dates/ explains the syntax you got from the macro recorder - it's about filtering date "groups" rather than specific dates (see "Multiple Date Groups").