I have a problem in a VBA code in Excel 2010.
I try to set a date filter criteria, which is not "equal to", but "is before or equal to" a date defined in a variable named 'Crit_Date'. I use the following VBA line:

Selection.AutoFilter field:=6, Criteria1:="<=" & Crit_Date

The problem is that the filter doesn't show any results even though there are valid data for that criteria.

When I afterwards (after running the macro) go to Excel and press the filter button, and click on "Date Filters" and click on "Custom Filter...", I see that the filter is defined correctly by the macro. When I then hit the "OK" button, the filter seems to work fine.

So the question is: How can I set a criteria using a macro, which doesn't work when running from the macro even though it is set correctly? And what can I do?

I've frequently seen problems in VBA Autofiltering a DATE column. Typically, I insure the date is formatted exactly the same as the column, that eliminates most of the problems.

Also, you should eliminate the "selecting" part of the macro, selecting and activating are human requirements for working on a sheet, not VBA requirements. You can send Autofilter commands directly to a sheet without ever activating it:


With Sheets("Sheet1")
.AutoFiltermode = False 'remove prior filters
.Rows(1).AutoFilter 'turn on new filter
.Rows(1).AutoFilter Field:=6, Criteria1:="<=" & Format(Crit_Date, "MM-DD-YYYY")
End With

Thanks, but it didn't solve my problem.
The strange thing about this is, that when I look in the defined filters after running the macro, the date criteria is set perfectly fine and works fine if I then pross OK. But it doesn't work directly from the macro.