Autofilter hides all rows when I set a date-based filter from Excel VBA

Question

Here's a simplified model of my task:

I would like to filter this table to show only the entries after 22-Jul. I can get the expected result if I manually apply a filter and use the following values in the menus / dialog box:

"Date Filters"
"After..."
"is after" and "22/7/2020"

But when I use this code (starting with the autofilter turned off) ...

Dim xx As Date
xx = Cells(4, 1)
ActiveSheet.Range("$A:$B").AutoFilter Field:=1, Criteria1:=">" & xx, Operator:=xlAnd

... excel filters out ALL the rows.

Trying to debug, if I click the autofilter drop down button in column A, it looks like Excel populated exactly the same options as I had done manually ... and if I click through to the dialog box and click OK (without changing anything at all), the filter behaves as expected.

Is there any way I can make VBA apply the autofilter correctly?

In the actual application, I only need simple functionality (apply different colours before / after a cutoff date). Worst case I could add a temporary Boolean column to the sheet and filter on that... but I'd be very grateful if someone could suggest a less clumsy method.

ActiveSheet.Range("$A:$c").AutoFilter Field:=3, Criteria1:="True", Operator:=xlAnd

If relevant, I'm in the United Kingdom with regional default date format = DD/MM/YY.

Record a macro while manually applying the filter and compare that to your existing code. — Tim Williams
Thanks for the fast reply Tim. Recording +then running a macro gives the same result: all rows are hidden. Then (after running the VBA) I go to the spreadsheet and click the Autofilter dropdown button in A1, then hover over 'Date Filters' (noting that there's already a tick next to it), and a sub-menu comes up with a tick already next to 'After'. I click there, and then click OK to confirm what's already there ('is after' and '22/07/2020') ... so I have changed nothing at all, and the filter works. — LondonJustin
Of course it has the result you want - the question is how does the recorded code compare to your posted code? — Tim Williams
Hi Tim, Sorry I over-interpreted your question and thanks for the follow-up. Here's the recorded code: Sub Macro3() Columns("A:B").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$B$6").AutoFilter Field:=1, Criteria1:= ">22/07/2020", Operator:=xlAnd End Sub Since it gave the same results as my original posted code I assumed that it would not be useful to post the code (sufficient to say that the behaviour is the same). However, by prompting me to play with the code again, your question has helped me find my own solution which I am about to post below! — LondonJustin

LondonJustin LondonJustin · Accepted Answer · 2020-07-29T14:49:36

I’m the OP and have found a solution thanks to Tim’s prompts!

For absolute clarity, here is a model that more closely replicates my actual task, showing the dates in the format that I need to use (I use this format because the spreadsheet is shared between Europe and USA, and also because I believe that it may be more robust with VBA than DD/MM/YY or MM/DD/YY ... for this question I think it's important to show that Excel cannot misinterpret the date because of the commonly reported MMDD vs DDMM issues).

If I set the VBA recorder running and then manually set up an AutoFilter for date > 2020-Jul-22 (picking the date from the autofilter calendar tool), the result looks like this:

If I then turn off the Autofilter and re-run my recorded steps, I get this:

I also get the same result with my ‘cleaned’ code as shown in the original post above.

In order for the code to give me the intended result, it was just necessary to change the type of xx from Date to Long.

The updated code - which gives the correct result - is this:

  Dim xx As Long
  xx = Cells(3, 1)
  ActiveSheet.Range("$A:$B").AutoFilter Field:=1, Criteria1:=">" & xx, Operator:=xlAnd
  Debug.Print xx
End Sub

The immediate window displays the value of xx as '44033'.

I tried some other experiments, and I've found (at least with my regional settings) that if (A) I try to set an AutoFilter with a variable of type Date and (B) the date is after the 12th of any month, then Excel will display the filter as if I'd entered an invalid date. The general solution (to populate date filters using VBA) is to convert the date into a numeric data type such as Long or Single rather than Date.

Autofilter hides all rows when I set a date-based filter from Excel VBA

1 Answers