Autofilter with column formatted as date

excel vba date autofilter
Spurious picture Spurious · Nov 4, 2013 · Viewed 61.8k times · Source

I am using an AutoFilter with VBA in Excel that works for regular filters, but not a column formatted as date.

I can filter it manually. 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 filters correctly.

Here is my code:

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

It seems to be a common problem, but I have not found a solution.

When I run a recorded macro, it does not work either.

Answer

Ron Rosenfeld picture Ron Rosenfeld · Nov 4, 2013

Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.

Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"

Criteria1:= ">" & CDbl([datecell])
Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))

Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.

Related questions

How to find the difference between dates in VBA

I am trying to find out the difference between the system date and the date stored in the worksheet. If the difference between them is > 30 days, the result is true, else the result is false Dim result as boolean …

Read More
SQL Query between dates in VBA

I've a problem making a sql query in VBA using excel and an access database. In mi VBA code I've two data variables with this content: DateMin = 31/07/2014 22:00:00 DateMax = 01/08/2014 06:00:00 And I have an access database with many data with a date …

Read More
How to check if a date cell in Excel is empty?

If feels like this should be really easy but I dont get it to work without retrieving the value of the cell again. To start with, I have 2 date cells: Dim agreedDate As Date Dim completedDate As Date THIS WORKS .. (…

Read More