Recorded Autofilter Macro Using Array With Date

  • royUK - my issue (as i think i have been stating) is that the code fails for me on the below line


    Code
    .Range("A1").AutoFilter Field:=1, Operator:=xlFilterValues, _
    Criteria2:=Array(2, "3/1/2021", 2, "2/25/2021", 2, "2/24/2021", 2, "2/23/2021")


    i get an error popup message box:

    Microsoft Visual Basic

    Run-time error '1004'

    AutoFilter method of Range class failed

  • just worked for me on a 3rd PC, also running win10 but excel 2010. as far as I can tell, I am the only one in this thread trying the code on Excel version 2016 (16.0) (Build 13628.20380) 32 bit (rory said he used Excel 2016 but didn't say what build he used).

  • 16.0.4954.1000 32bit

    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

  • rory - how do you know you have 16.0.4954.1000 ?


    I have Microsoft Office Professional Plus 2016.

    When I click File, Account, the area labeled "About Excel" says:

    Version 2101 (Build 13628.20448 Click-to-Run)


    i don't know which of us has the "latest" version.


    I have gotten my previously stated versions (in posts above) by running:

    Code
    strVerFull = CreateObject("Scripting.FileSystemObject").GetFileVersion(Application.Path & "\excel.exe")
  • When I click File, Account, the area labeled "About Excel" has a button "About Excel ?" next to it; clicking it says:

    Microsoft Excel 2016 MSO (16.0.13628.20128)

  • just updated to Excel version 2016 (16.0) (Build 13801.20266) 32 bit (version obtained by running code in my post #29) but still get the same error.


    When I click File, Account, the area labeled "About Excel" says:

    Version 2102 (Build 13801.20266 Click-to-Run)


    When I click File, Account, the area labeled "About Excel" has a button "About Excel ?" next to it; clicking it says:

    Microsoft Excel 2016 MSO (16.0.13801.20240)

  • on a Windows 10 Home PC (ver 2009 Build 19042.868 64 bit) and Excel 2016 (Build 12527.21686 32 bit), the code works. Upgrading to Excel 13801.20294 and it does NOT work - get the same error message "Run-time error '1004' AutoFilter method of Range class failed"

  • Hello,


    Had not seen your sample file ... sorry ...


    Based on your regional date format, you should test the following

    Code
    Sub FilterMacro()
    Dim myDate As Long
    myDate = DateSerial(2021, 2, 25)
    
        Sheet1.Range("$A$1:$A$5").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:=Array(2, Format(CStr(myDate), "m/d/yyyy;@"))
     
    End Sub


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

  • Once you have tested the macro ... or your test file, feel free to share your comments

    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. after viewing Carim's code in post #33, I see he's using Criteria1 while I was using Criteria2

    now my below code works (on Version 2102 - 09 March 2020 Build 13801.20294).

    Code
    Sheet1.Range("$A$1:$A$5").AutoFilter
    Field:=1,
    Operator:= _
    xlFilterValues, _
    Criteria1:=Array(2, "1/4/21", 2, "2/15/21", 2, "6/6/20" )


    I was using:

    Excel 2016 (part of Office Pro Plus 2016) (current channel - click to run) (Version 2102 - 09 March 2020 Build 13801.20294) 32 bit


    Downgraded to:

    Version 2006 (14 Jul 2020) Build 13001.20384 and Criteria2 works.


    Upgraded to:

    Version 2006 (28 July 2020 Build 13029.20498) and Criteria2 fails but Criteria1 works .


    So appears to me that it was a version issue caused by Microsoft.


    this answers my original question, so I guess this is closed; BUT, does anyone know where the correct documentation is ?

    https://docs.microsoft.com/en-…pi/excel.range.autofilter is dated 05/10/2019 and says:

    "The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria. Also used as single criteria on date fields filtering by date, month or year. Followed by an Array detailing the filtering Array(Level, Date). Where Level is 0-2 (year,month,date) and Date is one valid Date inside the filtering period."


    although the above code works, neither of the below do.


    'should show any row with month of Feb

    Sheet1.Range("$A$1:$A$7").AutoFilter Field:=1, Operator:= xlFilterValues, Criteria1:=Array(1, "2/25/21")


    'should show any row with year of 21

    Sheet1.Range("$A$1:$A$7").AutoFilter Field:=1, Operator:= xlFilterValues, Criteria1:=Array(0, "2/25/21")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!