Excel VBA AutoFilter: Filter Rows by a Specific Value

VBA & macros · Updated November 21, 2026

To filter a range by one exact value with Excel VBA, call AutoFilter on the range that includes the headers and set Criteria1 to the value. For example, dataRange.AutoFilter Field:=2, Criteria1:="=East" shows rows whose second field contains East.

What AutoFilter does

AutoFilter adds drop-down controls to a table’s header row and hides rows that do not meet the selected criteria. It does not delete or rearrange the underlying records: a filtered-out row remains in the worksheet, but is temporarily hidden. This makes AutoFilter useful when you want to inspect a subset of a list, such as orders for one region, without creating a separate copy of the data.

In VBA, the Range.AutoFilter method applies the filter. The range should normally include the header row and all columns that belong to the data set. The field number identifies the column to filter, and Criteria1 supplies the condition. Excel keeps the header visible and displays filter arrows for the range.

For instance, with headers in A1:D1 and records below, filtering field 2 by East means checking column B—not the second worksheet column in every possible layout. Understanding that distinction is the most important part of writing a reliable AutoFilter macro.

A basic macro for one exact value

Suppose the worksheet is named Data, row 1 contains headers, and the records occupy A1:D100. If the region is in column B, this macro filters the second field of that range to East:

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

    ' Set the worksheet and range, including the header row.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Keep rows where the second field equals East.
    dataRange.AutoFilter Field:=2, Criteria1:="=East"
End Sub

Change the worksheet name, range, field number, and criterion to match your workbook. If the range already has filters on other columns, this call may add the new condition while leaving those other conditions in place. Filter conditions on different fields work together, so a row must meet all active field conditions to remain visible.

Before running a macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z. In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11; on a Mac, use Option+F11 (some keyboards also require Fn). Excel for the web can display a workbook containing VBA, but it cannot run VBA macros.

How the Field number is counted

Field is a one-based position inside the range passed to AutoFilter. It is not automatically the worksheet’s column number. If the filter range is A1:D100, field 1 is column A, field 2 is B, field 3 is C, and field 4 is D. If the range begins in column C, field 1 refers to C, field 2 to D, and so on.

This is a common source of apparently incorrect results. For example, if your data occupies C1:F200 and you want to filter worksheet column E, use Field:=3, because E is the third column within C:F. Include all the columns that should stay together when rows are hidden; filtering only one column of a wider list can leave unrelated data visible beside the filtered records.

The first row of the range should contain unique, meaningful column headings. AutoFilter treats that first row as headers, not as a record to test. A missing header or an incorrect starting row can therefore make the field numbers seem shifted and can cause Excel to filter the wrong data.

Use a range that grows with the data

A fixed address such as A1:D100 is simple, but it will omit new records added below row 100. A common improvement is to find the last used row in a dependable key column. In this example, column A is assumed to contain a value on every record, and the data has four columns:

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

    ' Column A must be filled for every data record.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Include the header and all four data columns.
    If lastRow < 2 Then Exit Sub
    Set dataRange = ws.Range("A1:D" & lastRow)
    dataRange.AutoFilter Field:=2, Criteria1:="=East"
End Sub

The test for lastRow < 2 prevents trying to filter a header-only list as though it had records. Choose a column that is populated on every row: if column A has gaps near the bottom, Excel may stop at an earlier record and leave later rows outside the filter range. If the data has blank rows or irregular structure, use a more dependable key column or convert the list into an Excel table.

Filtering an Excel table

Tables are often the simplest choice for data that grows over time. A table expands when records are added directly beneath it, and its column names make the intended data structure easier to recognize. In this example, the table is named SalesTable, and its third column contains order status:

Sub FilterOpenOrders()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    ' Field 3 is the third table column, including the header layout.
    tbl.Range.AutoFilter Field:=3, Criteria1:="=Open"
End Sub

To create or rename a table in Excel for Microsoft 365 on Windows, select a cell in the data and use Insert > Table; use Table Design to view or change the table name. The Mac interface has the same table concepts, though ribbon placement can vary by version. When using a table, count fields from its first column rather than from worksheet column A.

Text, numbers, dates, and wildcard criteria

For text, an equals sign at the start of the criterion makes equality explicit: Criteria1:="=Open" asks for the value Open rather than text that merely begins with those letters. Text matching in Excel is generally not case-sensitive, so this criterion does not distinguish Open from OPEN. If your records contain leading or trailing spaces, apparently identical labels may not match as expected; clean or standardize the source data when exact consistency matters.

For a numeric field, use a numeric criterion. For example, with headers in A1:C100 and prices in column C, the field number is 3. This call filters for values equal to 125:

dataRange.AutoFilter Field:=3, Criteria1:=125

AutoFilter also supports comparison criteria such as greater than or less than, and text wildcards. An asterisk represents any number of characters, while a question mark represents one character. For example, Criteria1:="=North*" matches text beginning with North. A tilde can be used to treat a wildcard character as a literal. Wildcards are useful for deliberate partial matches, but use an equality criterion when you intend to select one whole label.

Dates need extra care because Excel stores dates as serial numbers while displaying them in regional formats. A date criterion written as text may be interpreted differently on computers with different regional settings. For portable automation, build the criterion from a VBA date value or a serial date and test it against the workbook’s date column. Also check whether cells contain true Excel dates or text that only looks like a date; AutoFilter treats those differently.

Filtering for more than one value or condition

To show two or more exact items from the same field, pass an array of values and use xlFilterValues. For example, this keeps records whose region is East or West when the filter range begins in column A and region is its second field:

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

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

For two comparison tests on the same field, such as values greater than one limit and less than another, AutoFilter also supports an operator such as xlAnd. Multiple fields are handled by calling AutoFilter for each field; the criteria combine as an intersection. For instance, filtering Region to East and Status to Open shows only East records that are also Open. Always check whether an earlier filter on another column is still active if the result looks unexpectedly narrow.

Clearing or replacing a filter

To remove the criteria while keeping the filter arrows, use the worksheet’s ShowAllData method when the sheet is currently filtered. Check FilterMode first because calling ShowAllData when there is no active filter can raise an error:

Sub ShowAllRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Clear criteria only when rows are currently filtered.
    If ws.FilterMode Then ws.ShowAllData
End Sub

To remove the AutoFilter arrows as well, use ws.AutoFilterMode = False. That removes the worksheet’s AutoFilter controls; it is different from showing all rows while retaining the controls. If the intention is only to change the value on one field, apply a new criterion to that field rather than clearing all filters. This preserves useful conditions on other columns.

Common problems and how to diagnose them

  • The wrong column is filtered: Recount Field from the left edge of the selected range. It is a range-relative index.
  • No rows match: Check spelling, spaces, data type, and whether the criterion is a full-value match or a wildcard pattern. A number stored as text may not behave like a numeric cell.
  • New rows do not appear: Expand the fixed range or use a table. A dynamic range based on a key column can also help, provided that column is filled for every record.
  • Some records seem to disappear unexpectedly: Check filters on other fields. Conditions on separate columns combine, so a row has to meet each active condition.
  • Headers or first records are treated incorrectly: Confirm that the range begins at the header row and that the header row has one heading per column.
  • The macro errors at ShowAllData: Test FilterMode before calling it. There may be filter arrows without any currently filtered-out rows.

Also look for merged cells, blank rows splitting a list, and inconsistent column contents. AutoFilter works best with one rectangular data set: a single header row followed by records in consistent columns. Formatting alone does not make a value equal to another value, and hidden rows created manually can make the visible result confusing.

Running macros safely on Windows and Mac

Save the workbook as a macro-enabled Excel workbook (.xlsm) if it needs to retain VBA. On Windows, the Developer tab may need to be enabled through File > Options > Customize Ribbon. Open Developer > Visual Basic to add code to a standard module, or use Alt+F11 to open the editor. Run a selected macro with Developer > Macros or Alt+F8.

On Mac, VBA is available in the desktop Excel app, but keyboard shortcuts and ribbon layout can differ; Option+F11 opens the Visual Basic Editor on many keyboards, sometimes with Fn. Excel for the web does not execute VBA. A Windows-only file path, API call, or other platform-specific code may also need adjustment on Mac, although the basic AutoFilter method shown here is available in desktop Excel for both platforms.

For ordinary worksheet filtering, Ctrl+Shift+L toggles AutoFilter on Windows. In Excel for Mac, Command+Shift+F is the common equivalent; keyboard mappings can vary with Excel version and system settings. The ribbon route Data > Filter is a dependable alternative on either desktop platform. These controls help inspect a filter manually, but they do not replace a macro when filtering needs to happen automatically.

Frequently Asked Questions

How do I filter a column for an exact value in VBA?
Call AutoFilter on a range that includes its header row, then set the field position and criterion, such as dataRange.AutoFilter Field:=2, Criteria1:="=East". Field 2 means the second column inside dataRange.

Can AutoFilter match part of a value?
Yes. Use wildcards in the criterion: an asterisk matches any number of characters and a question mark matches one character. For example, "=North*" matches values that start with North. Use a plain equality criterion for a whole-value match.

Does Field refer to the worksheet column number?
No. It counts from the first column of the range passed to AutoFilter. If the range starts in C, Field 1 means worksheet column C, not column A.

How can I remove a filter but keep the arrows?
When the worksheet is filtered, call ShowAllData on the worksheet. To avoid an error when no rows are currently filtered, check FilterMode first. Setting AutoFilterMode to False removes the arrows too.

Can VBA AutoFilter run in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to execute the macro; web users can still filter data using Excel’s built-in interface where available.

Related

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.