Posts by omiket

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

    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"

    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)

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

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

    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

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

    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

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

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

    Code
    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

    :?: