VBA AutoFilter Method of Range Class Failed?

“AutoFilter method of Range class failed” is usually Excel’s run-time error 1004: VBA tried to apply a filter to a range or worksheet state that Excel could not use. Check that the target range includes a valid header row, that the Field number falls within that range, and that the sheet is not protected; the exact cause depends on the line of code and workbook layout.

What the error means

The message identifies the operation that failed, not the specific reason it failed. In a statement such as filterRange.AutoFilter Field:=3, Criteria1:="Open", Excel is being asked to add or change a filter on filterRange. Error 1004 means Excel rejected that operation. It does not, by itself, prove that the filter criteria are invalid.

The first useful clue is the highlighted line in the Visual Basic Editor. If the error occurs on Range.AutoFilter, investigate the range, field index, worksheet state, and Excel platform. If the highlighted line is actually a different operation, such as selecting a sheet or setting a range, diagnose that line instead. A line-by-line check is more reliable than changing filter criteria at random.

How Range.AutoFilter works

AutoFilter applies drop-down filters to a list. A typical list has one header row followed by records, with each column representing a field. The range supplied to the method defines which headers and records Excel treats as the list. For example, if the range is A1:D100, its first field is column A, its second is B, and its fourth is D.

The Field argument is a one-based position within the filtered range, not an absolute worksheet column number. Thus, Field:=2 means the second column of the range even when that range starts in column F. If the range contains four columns, a field value of 5 is outside the list and cannot be used.

The method can show filter arrows or apply criteria. For instance, calling filterRange.AutoFilter with no arguments is commonly used to toggle the arrows for that list, while supplying Field and Criteria1 applies a condition. Be careful with a no-argument call: depending on the existing state, it may turn arrows off rather than on.

Common causes to check first

The range is not the list you expect. A blank row or column can split a data block. CurrentRegion stops at blank rows and columns, so it may return only part of a list or just a single cell.
The field number is out of range. Compare the requested field with filterRange.Columns.Count. Remember that field numbering starts at 1 relative to the range.
The header row is missing or unsuitable. AutoFilter expects column headings. A range beginning on the first record can make that record behave like a header, leading to confusing results. Merged headings and inconsistent layouts can also make a list difficult to filter reliably.
The worksheet is protected. Protection settings can prevent filter changes. Review the sheet’s protection and whether filtering is permitted; do not remove protection unless you are authorized to do so.
The code targets the wrong sheet or workbook. Unqualified references such as Range("A1:D100") depend on the active sheet. The macro may be running while another workbook or worksheet is active.
The filter state is unexpected. Existing arrows or criteria may belong to another range. A macro that assumes a clean sheet can fail or act on a different list than intended.
The code is running in an unsupported environment. VBA does not run in Excel for the web, and the Range version of AutoFilter has a Mac-specific limitation described below.

Diagnose the range and field number

Pause at the failing line and inspect the actual range. In the Visual Basic Editor, use the Immediate window to evaluate expressions such as ?filterRange.Address, ?filterRange.Rows.Count, and ?filterRange.Columns.Count. These checks help reveal whether the macro found the full table, only a fragment, or a range with fewer columns than the requested field.

Also check the data on the worksheet itself. Confirm that the top row of the intended range contains headings, that the records are arranged in a continuous rectangle, and that the field you intend to filter is included. If a blank line separates the heading from the data, or if a title sits above the headings, use an explicit range that starts on the actual header row instead of relying on an automatically detected region.

Use explicit worksheet references rather than relying on the active sheet. For example, ThisWorkbook.Worksheets("Orders").Range("A1:D100") identifies a particular worksheet in the workbook containing the macro. This is safer than Range("A1:D100"), whose target changes when the active sheet changes. If the data has a variable number of rows, calculate or detect the final row carefully rather than assuming a fixed address that may no longer cover the list.

A defensive Windows VBA example

This example assumes an Orders worksheet with headings in row 1 and a continuous list beginning in A1. It filters the third field of the detected region for the value Open. If the region is too small or does not contain that field, the macro stops with a message rather than trying an impossible filter.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a filter macro may change which records are visible.

Sub FilterOrdersByStatus()
    Dim ws As Worksheet
    Dim filterRange As Range

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set filterRange = ws.Range("A1").CurrentRegion

    ' Require a header, at least one record, and three fields.
    If filterRange.Rows.Count < 2 Then
        MsgBox "Add a header row and at least one data row.", vbExclamation
        Exit Sub
    End If

    If filterRange.Columns.Count < 3 Then
        MsgBox "The filter range does not include field 3.", vbExclamation
        Exit Sub
    End If

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

CurrentRegion is convenient when the list is a solid block, but it is not a universal way to find data. If the sheet contains blank separator rows, notes, or adjacent blocks, define the intended range explicitly or use an Excel table. The example also assumes the status field really is the third field in the detected range; change the field number to match the actual layout.

Existing filters, tables, and clearing criteria

Excel worksheets and Excel tables both support filtering, but their objects are not interchangeable. A table created with Insert > Table has a ListObject and its own AutoFilter. If the data is already a table, using that table’s range or filter object can make the target clearer than reconstructing a range from worksheet cells. Avoid applying a second, overlapping filter range without first confirming which list the code should control.

To remove criteria while keeping the drop-down arrows, check whether the worksheet is currently filtered before calling ShowAllData. The following statement is suitable when ws is a declared worksheet variable:

If ws.FilterMode Then ws.ShowAllData

FilterMode indicates whether the sheet is currently displaying a filtered subset; AutoFilterMode indicates whether AutoFilter arrows are displayed. They describe different states. Clearing criteria is not the same as removing the arrows, so decide which result the macro needs before changing either state. As with applying a filter, qualify the worksheet explicitly.

Criteria and range details that affect results

Once the method runs, the criteria must still match the data. A text criterion such as "Open" filters for that text value; a spelling difference, extra spaces, or a different status label can produce no visible records without causing a method failure. Wildcards such as * can be useful for text matches, but they change what counts as a match. For dates and numbers, ensure the source values are real Excel dates or numbers rather than text that merely looks like one.

For combined conditions, AutoFilter accepts additional arguments such as Operator and Criteria2. Use the documented Excel constants, such as xlAnd or xlOr, when the filter requires two conditions. More complicated criteria are easier to maintain when each condition is tested on a small sample first. Changing an operator will not fix a range that excludes the relevant column or has an invalid field index.

Another common misconception is that a filter deletes records. AutoFilter normally hides rows that do not meet the criteria; it does not remove those records from the worksheet. A macro that subsequently copies or deletes visible rows is a separate operation and needs its own checks, especially if the filter returns no matches.

Excel for Mac and Excel for the web

This guidance is primarily for Excel for Microsoft 365 on Windows. Microsoft documents a limitation for Range.AutoFilter in Excel for Mac: that method is not supported there, while similar methods on Selection and ListObject are supported. If the same macro works on Windows but fails on a Mac, this platform difference is a key possibility. For a Mac workbook, consider using a table-based approach or a supported Selection method, and check the behavior in the specific Excel version before relying on it.

Excel for the web can display and use worksheet filters, but it does not execute VBA macros. Run VBA in the desktop Excel application. A workbook stored online may still be opened in desktop Excel, but simply opening it in a browser will not make a VBA procedure run.

When to use a table or another approach

If a list grows over time, an Excel table is often more dependable than a fixed address or an assumption that CurrentRegion will always identify the data. Tables expand as records are added and keep their headers associated with the list. They also make the intended data object clearer in VBA. A carefully qualified range remains reasonable for a small, stable list, but it should be based on the actual header and record boundaries.

For a one-time task, the ribbon may be simpler than a macro: select a cell in the list and choose Data > Filter, then use a column’s arrow to choose criteria. In VBA, the best approach depends on whether the workbook uses a plain range or a table, whether the code must run on Windows and Mac, and whether the list changes size. If the error persists after checking those factors, reduce the workbook to a copy with the smallest failing range and inspect the exact failing statement and Excel version.

Frequently Asked Questions

What does Run-time error 1004 mean for AutoFilter?
It means Excel could not complete the AutoFilter operation as requested. The message alone does not identify whether the cause is the range, field number, protection, filter state, or platform. Inspect the highlighted line and the range’s dimensions first.

How do I know what Field number to use?
Count columns from left to right within the range passed to AutoFilter, starting with 1. If the range begins in column C, its first field is still 1, not 3. The field number must not exceed the range’s column count.

Why does CurrentRegion return the wrong range?
CurrentRegion stops at blank rows and columns. A gap inside the list can cause it to return only part of the data, while adjacent content can make it include more than intended. Check its address or specify the exact range or table.

Does AutoFilter delete rows that do not match?
No. It hides rows that fail the criteria. The records remain in the worksheet and become visible again when the filter is cleared, unless separate code deletes them.

Why does Range.AutoFilter fail on a Mac?
The Range version of the method is documented as unsupported in Excel for Mac. Use a supported table or Selection-based method where appropriate, and confirm compatibility in the target Mac version. VBA itself cannot run in Excel for the web.

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.