Recorded Autofilter Macro Using Array With Date

[xpost][/xpost]

Hi

I am using:

Excel version 2016 (16.0) (Build 13628.20380) 32 bit

Windows 10 Pro version 2009 (20H2) (Oct 2020) Build 19042 64 bit

i am trying to write vba code to filter on multiple non consecutive dates, but I haven't been successful.

so I did what I thought should be very simple:

I have a worksheet with the below data starting in cell(1,1) (rows with date entries are formatted as m/d/yyyy):

date

3/1/2021

2/25/2021

2/24/2021

2/23/2021

When I use the macro recorder to create an autofilter macro, it creates it as:

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "2/25/2021")

and the data is correctly filtered. but when I remove the filter and then manually run the macro, it gives error:

Number: 1004

Description: AutoFilter method of Range class failed

I have tried multiple other ways but can't get any autofilter with dates and array to work.

I have read MANY googled items, but haven't found the solution.

Anyone know what's wrong ?

Thanks

thanks Carim. I just tried:

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _

xlFilterValues, Criteria2:=Array(1, DateSerial(2021, 2, 25))

and (because https://docs.microsoft.com/en-…pi/excel.range.autofilter says the criteria must be a string)

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _

xlFilterValues, Criteria2:=Array(1, CStr(DateSerial(2021, 2, 25)))

both produce the same error.

Other suggestion(s) ?

thanks Carim. I just attached my workbook. it has one vba Sub called Macro. i also tried the code suggested by royUK, but with the same error result.

Book1.xlsm

thanks royUK - i am simply trying to learn how to write vba code to filter on multiple non consecutive dates, that is why my sample data is so simple.

what version of excel & windows are you using ?

I am using:

Excel version 2016 (16.0) (Build 13628.20380) 32 bit

Windows 10 Pro version 2009 (20H2) (Oct 2020) Build 19042 64 bit

thanks royUK and Carim for trying to help.

I don't understand how to be more clear. I don't understand why none of the below code statements don't work.

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "2/25/2021")

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, DateSerial(2021, 2, 25))

ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, CStr(DateSerial(2021, 2, 25))

In case my windows profile is corrupted, I just tried it on my PC using another user account - same error.

in case something about my windows or excel installation is corrupt, I just tried it on my 2nd PC (same version of windows and excel)- same error.

the exact error is:

an error popup message box:

Microsoft Visual Basic

Run-time error '1004'

AutoFilter method of Range class failed

clicking the HELP button on the message box takes me to https://docs.microsoft.com/en-…-home?redirectedfrom=MSDN which doesnt help me understand what is wrong.

were you able to open my attached Book1.xlsm ?

if so, did you try to run the VBA code "Macro"?

if so, did it work ?

if yes to the above 3 questions doesn't that mean there is something different about my PC (windows and \ or Excel) and yours ?

Thanks

thanks royUK. Yes, i tried your code \ workbook as in your post #7 above. I get the same error. I agree that the code in your post #12 above creates the same error (but because it is attempting to filter on Column 3 which is not part of the data table); that's why i appended it with:

'OP - since there is no data in field 3 this also fails (which makes sense to me) & dont understand why royUK suggested it.

thanks Carim. Please see my attached workbook Book2.xlsm.

Sheet 'OriginalData' shows exactly that: the original data unfiltered.

Sheet 'FilteredDataManually' shows exactly that: the data filtered by manually clicking the autofilter drop down arrow, deselecting 'Select All', and selecting 'Feb 2021'.

I want to get the same results on Sheet 'FilteredDataVBA' by running the vba sub named Macro.Book2.xlsm

rory - you asked 'What are your Windows regional settings?'; I am using english language and what i think are the default settings (as shown in RegionSettings.bmp):RegionSettings.bmp

royUK - the code in your post #15 gives me the same error.

you're using excel office 365; i am using Excel version 2016 (16.0) (Build 13628.20380) 32 bit.

your post #9 says that shouldn't matter; i agree that it shouldn't, but there are (probably) millions of examples where different versions of software don't operate the same. Isn't that the classic definition of 'bugs' ?