locked
Error with grouped dates in Autofilter only Excel 2019 RRS feed

  • Question

  • Hello,

    In column 1 there is a list of dates (from the years 2020 and 2021).

    In Excel 2010 and Excel 2019 this code works: 

      ActiveSheet.Range("$A$1:$B$15").AutoFilter Field:=1, Criteria1:= _

            ">=12-1-2020", Operator:=xlAnd, Criteria2:="<=1-8-2021"

    In Excel 2010 (32- and 64 bits version) this code works also as expected:

        ActiveSheet.Range("$A$1:$B$15").AutoFilter Field:=1, Operator:= _

            xlFilterValues, Criteria2:=Array(0, "5/2/2021", 2, "11/15/2020", 2, "12/13/2020")

    (that shows all of year 2021 and the dates 11/15/2020 and 12/13/2020)

    but in Excel 2019 this code results in the error 1004: Autofilter of Class Range has failed (translated from Dutch).

    And as far as I remember, a year ago this was not yet a problem in Excel 2019.

    Is this only me and /or did I do something that resulted in this behaviour or is it a (well known) bug?

    Jan

    Monday, December 21, 2020 2:06 PM

All replies

  • re:  filtering dates

    Are the Date settings in Windows identical on all computers?
    Is the "use system separators" setting identical in each Excel version?

    '---
    NLtL
    https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA
    Monday, December 21, 2020 5:07 PM
  • NLtL,

    Thank you for your reply.

    Yes, both are identical on the different computers.

    So, I hope there will be someone (you or someone else) recognizing the issue.

    Jan

    Monday, December 21, 2020 7:46 PM
  • Both your AutoField code lines worked for me in 365, which is effectively 2019. It may have failed for you for some other reason in the given sheet with data we can't see. Maybe upload it to a file sharing site.

    In passing, if you are working with a Dutch system normally your dates would be in DMY order, but I see your hard coded dates are US MDY. That might make the your date 5/2/2021 ambiguous, is that 5-Feb or May-2.

    My system dates are DMY order, after running your code your 11/15/2021 and 12/13/2020 got converted to DMY order 15/11/2020 and 13/12/2020 as expected, your 5/2/2020 was unchanged.


    Wednesday, December 23, 2020 3:09 PM
  • Peter,

    Thank you for testing the code in version 365.

    I tested it in a very simple workbook with 1 sheet and only a little table with dates. The same workbook in Excel 2010 the test passes with no problem. In Excel 2019 on two computers went wrong.

    I ‘ve changed the dateformat in all possible ways (I think) but with no success.

    Next year I will have an other go.

    Jan

    Wednesday, December 23, 2020 8:35 PM