Excel VBA Autofilter not working with Date column

Question

I got a problem using AutoFilter with VBA in Excel.

It works well for regular filters, but filtering the date column does not work as intended. The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.

Here is my code:

ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
        :=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
        "<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))

Anyone has an idea? It seems to be a common problem, but I have not found a solution.

Thanks in advance.

Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.

Answer 1

you need to convert the format to the american format, like: ">" & Format([datecell], "mm/dd/yyyy") VBA does not understand another format.

Answer 2

This syntax works for me:

.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))

Hint obtained through a macro registration

Answer 3

Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"