Fix Excel VBA AutoFilter Error 1004 When Filtering Dates
Question details
Fix an Excel VBA macro that raises error 1004 when AutoFilter applies date criteria.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro filters a date column using Criteria2 after an Office or Windows update.
- Observed behavior
- Excel reports “AutoFilter method of Range class failed” when the macro applies the filter.
Open the workbook in Excel and identify the header row, the full filter range, and the date column. Check whether the date cells contain actual Excel dates rather than text, and test changes in a copy of the workbook.
Replace locale-dependent date criteria with serial date bounds
Use this approach when the macro relies on hard-coded date text that may be interpreted differently after an update.
The example uses numeric date bounds with xlAnd instead of the special Criteria2 date-grouping array. The upper bound is exclusive, so it also includes times on the final day.
AutoFilter behavior can vary by Excel version and data type. If serial criteria do not work on the date column, try the numeric helper column in the next solution.
In the worksheet, confirm that the filter range includes a header row and all data rows. Field 6 means the sixth column within that range, not necessarily worksheet column F.
Press Alt+F11, open the affected module, and adapt this example to your sheet, range, and dates: Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") If ws.FilterMode Then ws.ShowAllData ws.Range("A1:F500").AutoFilter Field:=6, _ Criteria1:=">=" & CLng(DateSerial(2025, 1, 1)), _ Operator:=xlAnd, _ Criteria2:="<" & CLng(DateSerial(2025, 2, 1))
Run the macro from the VBA editor using F5. Check that the filtered rows fall in the intended date range and that the range includes all relevant data.
In Excel, select File > Account > Update Options > Update Now, if available. Reopen the workbook and retest the macro.

Filter a numeric helper column for normalized dates
Use a helper column if filtering the original date column still fails or its values include times.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet work, with a familiar interface and Excel-format compatibility to help you move existing files. It does not repair Excel's VBA AutoFilter error, and macros may not behave the same way.

Frequently Asked Questions
What does Field 6 mean in an AutoFilter macro?
Field 6 is the sixth column of the range passed to AutoFilter. For example, in Range("B1:G500"), Field 6 refers to worksheet column G.
How can I tell whether my dates are stored as text?
In an empty worksheet cell, enter =ISNUMBER(F2), replacing F2 with a date cell. TRUE indicates a numeric Excel value; FALSE indicates that it is not a numeric date value.
Why use an exclusive end date in the filter?
Using a criterion such as less than February 1 includes all of January 31, even when cells contain times. A less-than-or-equal-to criterion for January 31 at midnight may exclude later times that day.
How do I clear an existing filter before running the macro?
For a worksheet filter, use If ws.FilterMode Then ws.ShowAllData before applying the new AutoFilter. Check any Excel table filters separately if your data is in a table.
Will updating Office always fix error 1004?
Not necessarily. In Excel, check File > Account > Update Options > Update Now, then test the macro. Also verify the filter range, field number, headers, and date criteria.




