Excel VBA Filter Range: AutoFilter Examples and Fixes

To filter a range with Excel VBA, use the range’s AutoFilter method and specify the field number and criterion. For example, if a range with headers is in A1:D100 and the third column contains regions, Range("A1:D100").AutoFilter Field:=3, Criteria1:="West" displays rows whose region is West.

How VBA filtering works

Excel’s AutoFilter hides rows that do not meet the criteria; it does not delete them or move matching rows to a new location. You can still edit the visible records, and the hidden records remain part of the worksheet. This makes AutoFilter useful for reviewing, updating, or reporting on a subset of a larger list.

The range passed to AutoFilter should include the header row and the data rows to filter. Excel uses the first row of that range for the filter headings. The Field argument identifies a column by its position within the selected range, not by its worksheet column letter. Therefore, if the range starts at column C, Field:=1 means column C; it does not mean column A.

AutoFilter adds or uses drop-down arrows in the header row. A VBA criterion applies a filter in the same way as choosing a value or condition from a header’s drop-down menu. You can filter one field or apply criteria to several fields; when you filter different fields, Excel normally shows rows that satisfy the criteria for all of them.

Set the range and field correctly

Suppose row 1 contains headers in columns A through D: Order ID, Date, Region, and Amount. The data extends through row 100. The range is A1:D100, so the field numbers are A = 1, B = 2, C = 3, and D = 4. To show only West-region orders, use field 3. If you instead passed B1:D100, Region would be field 2 because it is the second column in that range.

Hard-coding the last row, as in A1:D100, is simple when the data size is fixed. For a list that grows, determine the final row at run time or use an Excel Table. A table expands when new rows are added and maintains its own filter controls, which can make a macro less dependent on a fixed row count. Whichever approach you choose, make sure the range covers every intended column and every data row, and does not accidentally include unrelated cells below or beside the list.

Apply a basic filter with VBA

This example filters a fixed range for West in its third field. Change the sheet name, range, field, and criterion to match your workbook. The macro assumes row 1 is the header row and that the list is on a worksheet named Data.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even when a macro only filters rows. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, then run the procedure. On Mac, use Option+F11 (on some keyboards, Fn+Option+F11) to open the editor; Excel for the web cannot run VBA macros.

Sub FilterWestRegion()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Set the worksheet and the header-plus-data range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Show rows where the third field equals West.
    dataRange.AutoFilter Field:=3, Criteria1:="West"
End Sub

Running the macro again with a different criterion replaces the filter on that field. To filter another field too, call AutoFilter again with that field number and its own criterion. For example, a filter on field 3 for West and field 4 for an amount greater than 1000 narrows the visible rows to those meeting both conditions.

Use text, numbers, and multiple criteria

The Criteria1 argument can represent an exact text value or a comparison condition. For numbers, comparison operators such as greater than, less than, and not equal to are included in the criterion string. Text criteria can also use wildcard characters: an asterisk matches a sequence of characters, and a question mark matches one character. Use a tilde before a wildcard when you want to match the character itself rather than treat it as a wildcard.

Criteria1:="West" filters for the text West.
Criteria1:=">1000" filters numeric values greater than 1000.
Criteria1:="<>" filters for nonblank cells in the field.
Criteria1:="North*" matches text beginning with North, such as Northeast.

For two conditions on the same field, use Operator:=xlAnd or Operator:=xlOr with Criteria1 and Criteria2. For example, to show amounts from 500 through 2000, set both a lower and upper bound using xlAnd. To select either of two exact text values, an array with xlFilterValues is often clearer:

Sub FilterTwoRegions()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Update these references for your worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Show rows for either listed region in field 3.
    dataRange.AutoFilter Field:=3, _
        Criteria1:=Array("East", "West"), Operator:=xlFilterValues
End Sub

Date filters need extra care because dates may be displayed differently under different regional settings. Excel stores dates as serial values, while a typed date string can be interpreted according to the computer’s locale. For date-based macros, confirm the workbook’s date column contains real Excel dates rather than text, and test the criterion with the workbook’s date format. Also check whether the intended end date is inclusive; for a full day in a column that contains times, a condition ending at midnight may omit later times on that date.

Clear a filter without removing its arrows

There is an important difference between clearing a filter and removing AutoFilter. If you want all rows visible but want to keep the header drop-down arrows, use the worksheet’s ShowAllData method when a filter is currently hiding rows. The FilterMode check prevents an error when no filter is active:

Sub ClearActiveFilters()
    Dim ws As Worksheet

    ' Use the worksheet that contains the filtered list.
    Set ws = ThisWorkbook.Worksheets("Data")

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

By contrast, AutoFilterMode reports whether the worksheet displays AutoFilter arrows. Setting that property to False removes the arrows, so it is not the same as showing all rows while preserving the filter controls. Be deliberate about which result you want: reset the criteria for a clean view, or remove the controls altogether.

Choose between a worksheet range and an Excel Table

A normal range works well for a stable data block or a one-off macro. Its main drawback is maintenance: if the list grows beyond the address in the code, new rows will not be included. You can calculate the last used row in VBA, but that calculation must be based on a column that is reliably populated and should account for blank rows or other content on the sheet.

An Excel Table is often a better choice for an ongoing list. Tables have structured headers, expand as data is added, and keep filtering connected to the table. VBA can refer to a table by its name and use its Range or DataBodyRange, depending on whether the operation needs the header row. Avoid assuming that a table always has data rows: an empty table may have no usable data body range. Table references also make the purpose of a macro easier to understand than unexplained addresses such as A1:D100.

For a quick interactive task, the filter arrows may be enough without VBA. A macro is useful when the same filtering action must be repeated, triggered by a button, or combined with other steps. If the goal is to produce a separate, refreshable result rather than hide rows in the source list, consider Excel’s FILTER function instead. FILTER is available in Microsoft 365 and Excel 2021 or later, but it returns a formula-driven result and does not behave like VBA’s row-hiding AutoFilter.

Common problems and practical fixes

The wrong column is filtered: Recount Field from the left edge of the range. A range beginning in column B has a different field numbering from one beginning in column A.
Headers or the first record are treated incorrectly: Include the real header row at the top of the AutoFilter range. A blank or duplicate-looking header can make the drop-downs confusing; clean the headings before filtering.
New records are ignored: The code may use a fixed address that stops above the new rows. Expand the range, calculate its boundary carefully, or convert the list to a Table.
A criterion finds no rows: Check for leading or trailing spaces, inconsistent spelling, numbers stored as text, and dates stored as text. The filter does not automatically repair inconsistent source data.
ShowAllData raises an error: It can fail if there is no active filter hiding rows. Check FilterMode first, and ensure the worksheet variable points to the sheet whose list is filtered.
The macro runs but nothing happens: Confirm that macros are enabled for a trusted workbook, that the correct sheet and range are referenced, and that the criterion actually occurs in the selected field.

When diagnosing a filter, inspect the actual range in the sheet and compare it with the range in the code. Also check for hidden rows that were hidden manually before the macro ran. AutoFilter controls which records meet its criteria, but manually hidden rows can make it harder to judge the visible result. Test the procedure on a copy of the workbook before using it on an important data set.

Macro safety and platform notes

Macros can change workbook contents and are disabled or restricted in some environments for security. Only enable a macro when you trust its source and understand what it does. Save a separate copy before running unfamiliar code; Ctrl+Z cannot reverse a VBA procedure. Keep a clear workbook backup if a macro is part of a repeatable business process.

The examples here target desktop Excel for Microsoft 365 on Windows. Excel for Mac supports VBA, though keyboard shortcuts and some editor details differ; use the Mac shortcut noted above or open the Visual Basic Editor from Excel’s Developer tools. Excel for the web does not execute VBA, so a macro-based filter must be run in desktop Excel. The filtered workbook can still be opened in the browser, but the macro itself will not run there.

Frequently Asked Questions

How do I filter a range in Excel VBA?
Call AutoFilter on a range that includes the headers and data, then provide a field number and criterion, such as Range("A1:D100").AutoFilter Field:=3, Criteria1:="West". The field number is relative to the range’s first column.

Does VBA Field 1 mean column A?
Not necessarily. Field 1 means the first column in the range passed to AutoFilter. If the range starts at column C, field 1 refers to C.

How do I show all rows again but keep the filter arrows?
Use Worksheet.ShowAllData when the worksheet is in filter mode. Check Worksheet.FilterMode first so the macro does not try to clear a filter that is not active.

Can VBA filter for two values in one column?
Yes. For two exact values, pass an array through Criteria1 and use Operator:=xlFilterValues. For a range of values, use two criteria with xlAnd or xlOr, as appropriate.

Can I use VBA filters in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run the macro; ordinary worksheet filtering remains available separately from VBA.

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.