Excel VBA AutoFilter Method Failed? Causes and Fixes

Yes—“AutoFilter method of Range failed” is usually a VBA runtime error caused by an invalid or unsuitable range, an incorrect field number, or a conflict with the worksheet’s current filter state. On Windows, the most reliable fix is to call AutoFilter on the complete, contiguous data range, including its header row, and make sure the field number is counted from the left edge of that range.

What the AutoFilter method does

In Excel VBA, Range.AutoFilter applies filter dropdowns to a range and can also set filter criteria. A typical call names the range, the field to filter, and the criterion. The range should represent one rectangular list: one header row followed by records in consistently arranged columns. For example, if A1:D200 contains headers and records, field 3 means column C, not worksheet column C in every possible range.

The error message is broad. It does not prove that the AutoFilter feature is broken or that the workbook is damaged. VBA may be calling the method on a range that Excel cannot use in the current circumstances, or may be supplying an argument that does not match the range. Resolving the cause usually means inspecting the actual range and worksheet state at the line where execution stops.

Check the range and its header row

Start by checking the expression immediately before .AutoFilter. A frequent bug is building a range from a last-row value that is zero, one, or otherwise not what the macro expects. Another is filtering only the header cell, or choosing a range that leaves out some columns or records. Include the header row so Excel can associate each filter field with its column label.

For a reliable list, the range should be contiguous. Completely blank rows or columns inside the intended data can make automatic region detection stop early. Merged cells in the header or data area can also interfere with filtering. If the code uses CurrentRegion, inspect what it selects: it expands from a starting cell until it reaches blank rows or columns, so it may include too much or too little.

Confirm the worksheet and workbook are the intended ones; an unqualified Range can refer to the active sheet rather than the sheet containing the data.
Confirm the first row of the range contains the intended field names and is not itself a record row.
Check that the range covers all columns used by the filter and all rows that should be filtered.
Look for merged cells, blank separator rows, or unexpected changes to the sheet layout.

Qualifying the worksheet is important. A macro that works while one sheet is active can fail or filter the wrong list when another sheet becomes active. Prefer an explicit reference such as ws.Range("A1:D200") over an unqualified Range("A1:D200").

Count the Field argument from the range

The Field argument is a one-based position within the filtered range. If the range starts in column B, its first field is B, its second is C, and its third is D. A common source of failure is passing the worksheet column number as Field. For example, a range beginning at B1:D100 has only three fields; Field:=4 is outside that range, even though column D is the fourth column of the worksheet.

Check that the field number is at least 1 and no greater than the number of columns in the range. Also confirm that a variable used for the field has not been left at its default value of zero. With a range from A1:D100, Field:=3 filters column C. If you change the range to B1:D100, use Field:=2 to filter column C.

Criteria must also be supplied in the expected form. Text criteria are usually strings, while operators such as greater-than signs are part of the criterion string. For instance, Criteria1:=">100" requests values greater than 100. When filtering for an exact text value, check for leading or trailing spaces and differences in the actual cell contents.

Use a predictable range in VBA

This Windows example finds the last used row in column A and filters the third field of a four-column Orders list. It assumes row 1 contains headers, column A is populated for every record, and the data occupies columns A through D. The example clears existing filter arrows on that worksheet before applying the new filter; change that behavior if the sheet has a filter setup you need to preserve.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and clearing filter arrows can change the worksheet’s current filter setup.

Sub FilterOrders()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Keep the header row, but exit if there are no records.
    If lastRow < 2 Then Exit Sub

    Set dataRange = ws.Range("A1:D" & lastRow)

    ' Remove existing filter arrows before setting this range.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

If column A can be blank in valid records, finding the last row from that column can truncate the range. Choose a column guaranteed to contain a value in each record, or use another deliberate method to determine the data boundary. Do not assume that the visually last row is always the row returned by a particular column’s End(xlUp) calculation.

Diagnose the exact line and worksheet state

When the error appears, use the VBA editor’s Debug button to identify the highlighted line. Then inspect the values of the worksheet variable, range address, last-row calculation, and field number. Press F8 to step through the macro one line at a time on Windows; this can reveal whether the range is being created correctly before the filter call. On Mac, the VBA editor and available shortcuts can differ, so use its Step Into command if F8 is not assigned as expected.

Check whether another filter is already applied. AutoFilterMode indicates whether filter dropdown arrows are displayed; FilterMode indicates whether the worksheet is currently showing a filtered subset. They are not interchangeable. Calling ShowAllData when no filter is active can itself cause an error, so guard that call:

Sub ShowAllRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")

    ' Show all records only when a filter is currently hiding rows.
    If ws.FilterMode Then ws.ShowAllData
End Sub

Also check whether the sheet is protected, whether the macro is running on the expected workbook, and whether another procedure has changed the range or active sheet. Avoid suppressing the error with On Error Resume Next as a permanent fix. It can hide the original cause and allow later code to act on an unfiltered or incorrect list. If you use error handling for cleanup, report or re-raise errors rather than silently ignoring them.

Common filtering problems and their fixes

A filter that runs without an error can still produce an unexpected result. These are common situations worth distinguishing from a failed method call:

The wrong column is filtered: Recalculate Field relative to the range’s left edge. Do not use the worksheet column letter as the field number.
Some records are missing: Verify the range’s last row and look for blank separator rows that split a CurrentRegion.
No records remain visible: Check whether the criterion exactly matches the data, including spaces, spelling, and number-versus-text differences.
Blank cells are involved: Excel criteria such as "=" and "<>" are commonly used to select blanks and nonblanks, respectively; confirm that the chosen field and data are what you intend.
Dates do not match expectations: A cell may contain text that looks like a date rather than an Excel date value. Regional date settings and the way criteria are constructed can also affect results.

For two conditions on one field, VBA supports an operator such as xlAnd or xlOr. Be precise about the logic: “greater than 10 and less than 20” is different from “less than 10 or greater than 20.” When criteria are built from variables, inspect the final strings before applying them. This is especially useful when a criterion includes comparison symbols or dates.

Ranges, tables, and Excel for Mac

For a stable dataset that grows over time, an Excel Table is often easier to manage than a manually calculated range. Create one from the data with Insert > Table in Excel for Microsoft 365 on Windows, ensuring that the header option is selected when appropriate. Tables expand as records are added and maintain filter dropdowns, reducing the risk that a fixed range omits new rows. VBA can refer to a table by its worksheet and table name, rather than relying only on a changing row number.

Excel for the web can display and use workbook filters, but it does not run VBA macros. The VBA examples here are for desktop Excel. Excel for Mac also differs: Microsoft documents that the Range.AutoFilter method is not supported on Mac, while related filtering approaches may be available. If the same macro must run on Mac, verify the supported object and method for the installed Excel version rather than assuming Windows VBA behavior will transfer unchanged. A worksheet table’s built-in filter controls are an option when filtering manually.

Do not confuse a normal range filter with Advanced Filter. AutoFilter shows dropdown controls and hides rows that do not meet the criteria. Advanced Filter has different behavior and can copy matching records to another location. If the task is to extract or transform data rather than temporarily hide rows, a table filter, Advanced Filter, or a worksheet formula may fit better.

Frequently Asked Questions

Why does Range.AutoFilter fail with error 1004?
Error 1004 is a general Excel automation error. For AutoFilter, check that the range is valid and contiguous, includes headers, has a field number within its column count, and is being used in a supported Excel environment. The exact cause depends on the highlighted line and workbook state.

Does Field refer to a worksheet column number?
No. It refers to the field’s position within the range passed to AutoFilter, starting at 1 from that range’s leftmost column. A range beginning in column C still has field 1 in column C.

How do I remove an existing AutoFilter?
Setting a worksheet’s AutoFilterMode to False removes its filter arrows. To show all records while leaving the filter controls in place, check FilterMode and call ShowAllData only when a filter is active.

Can I run AutoFilter VBA in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel to run VBA; web users can use the filtering controls available in the browser.

Why does the macro work on Windows but fail on Mac?
Some Excel VBA methods differ by platform. Microsoft notes that Range.AutoFilter is unsupported on Mac, so code that calls that method directly may not be portable. Check the documentation for the target Mac version and use an approach supported there.

A practical troubleshooting order

Work from the simplest checks to the less obvious ones: identify the precise failing line, qualify the worksheet and workbook, inspect the complete range, verify the header and field index, and check the current filter and protection state. Then test a small, known-good range on a copy of the workbook. If that succeeds, compare it with the real dataset to find the structural difference. This approach is more dependable than repeatedly changing criteria or adding error-suppression statements without confirming what Excel is being asked to filter.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.