CODEWITHSUNDEEP
excelvbafilterautofilter
Senor Penguin
Senor Penguin

Reputation: 405

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

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")

Upvotes: 1

Views: 387

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

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").

Upvotes: 2

Related Questions