VBA & Macro Problems

Fix Excel VBA AutoFilter Error 1004 When Filtering Dates

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Fix an Excel VBA macro that raises error 1004 when AutoFilter applies date criteria.

Fix Excel VBA AutoFilter Error 1004 When Filtering Dates
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the filter range

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.

2
Edit the macro

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))

3
Test the result

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.

4
Check for Office updates

In Excel, select File > Account > Update Options > Update Now, if available. Reopen the workbook and retest the macro.

Replace locale-dependent date criteria with serial date bounds
Date-grouping criteria: Criteria2 can also take an array for Excel's date-grouping filters. The example above replaces that approach with a start and exclusive end bound; adjust the dates to match your original intent.
Free Microsoft Office alternative

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.

Filter and review spreadsheet data through a familiar interfaceOpen and work with Microsoft Excel formats such as XLSXMove existing spreadsheets to a lightweight Office alternativeUse core spreadsheet tools for free
microsoft office alternative - wps office

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.