Recorded Autofilter Macro Using Array With Date

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    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:

    Code
    1. ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
    2. 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

    :?:

  • Hello and Welcome to the Forum :)


    When it comes to using AutoFilter in VBA with Dates ...


    it is always safer to use DateSerial(year, month, day) in order to avoid any potential confusion ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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) ?

  • Since my crystal ball is broken ... I cannot :


    1. see what you have in front of your eyes ...

    2. guess what you want to accomplish ...

    3. test a working solution ...


    ... Thanks for attaching a sample file ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • You might be better to ensure AutoFilter is on. Try this, change the sheet name to match your requirements


    Code
    1. With Sheet1
    2. If Not .AutoFilterMode Then .Range("A1").AutoFilter
    3. .Range("A1").AutoFilter Field:=3, Criteria1:="a"
    4. .Range("A1").AutoFilter Field:=1, Operator:=xlFilterValues, _
    5. Criteria2:=Array(2, "3/1/2021", 2, "2/25/2021", 2, "2/24/2021", 2, "2/23/2021")
    6. End With
  • This works fine for me although you test data isn't much use


    Code
    1. With Sheet1
    2. If Not .AutoFilterMode Then .Range("A1").AutoFilter
    3. .Range("A1").AutoFilter Field:=1, Operator:=xlFilterValues, _
    4. Criteria2:=Array(2, "3/1/2021", 2, "2/25/2021", 2, "2/24/2021", 2, "2/23/2021")
    5. End With
  • 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

  • ... simply trying to learn how to write vba code to filter on multiple non consecutive dates ....


    Hello,


    Even what you are actually trying to test ...and learn ... is not very clear ...


    Why don't you elaborate a little with a bit more records ...( rows and columns ...) ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

    Code
    1. ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
    2. xlFilterValues, Criteria2:=Array(1, "2/25/2021")
    3. ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
    4. xlFilterValues, Criteria2:=Array(1, DateSerial(2021, 2, 25))
    5. ActiveSheet.range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
    6. 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

  • Did you try my code and workbook?


    This line errors in your workbook because it is attempting to filter on Column 3 which is not part of the data table


    Code
    1. .Range("A1").AutoFilter Field:=3, Criteria1:="a" 'OP - since there is no data in field 3 this also fails (which makes sense to me) & dont understand why royUK suggested it.
  • 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.

  • What are your Windows regional settings?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • That was in your code. This is the code that I posted which works on the limited data that you provide. I added some random items to the data for testing


    Code
    1. With Sheet1
    2. If Not .AutoFilterMode Then .Range("A1").AutoFilter
    3. .Range("A1").AutoFilter Field:=1, Operator:=xlFilterValues, _
    4. Criteria2:=Array(2, "3/1/2021", 2, "2/25/2021", 2, "2/24/2021", 2, "2/23/2021")
    5. End With
  • In message # 10 ...


    Quote

    Even what you are actually trying to test ...and learn ... is not very clear ...


    Why don't you elaborate a little with a bit more records ...( rows and columns ...)


    Build a more complete sample file ... with 10 to 20 records ...


    and a visual representation of the final result you are looking for ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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' ?

  • So what issue are you having with my code. I've changed it to run on each sheet and it filters with no errors


    There should definitely be no issues with the different versions

  • Your code runs fine for me with Excel 2016 on Win 10 Enterprise, in both UK and US regional formatting.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why