Excel VBA AutoFilter Based on a Cell Value: Examples

VBA & macros · Updated November 21, 2026

Yes. In VBA, pass the value of a worksheet cell to the Criteria1 argument of Range.AutoFilter, for example: filterRange.AutoFilter Field:=2, Criteria1:=criteriaCell.Value. Set Field to the column’s position within the filtered range, not its worksheet column number.

What AutoFilter does

Excel’s AutoFilter hides rows that do not match a condition; it does not delete them or rearrange the underlying data. In VBA, the method is called on the range that contains the headers and records. Its Field argument identifies which column to filter, and Criteria1 supplies the value or condition to match.

This is useful when a user chooses a department, status, product, or other value in a control cell and wants a macro to show only matching records. For example, if a report has a Region column and cell F2 contains West, a macro can filter the records to show West. Changing F2 alone does not automatically update the filter: the macro must run again, or worksheet event code must call the filtering procedure when the cell changes.

The code below assumes a worksheet named Data, a header row in row 1, a four-column dataset in A1:D100, and a criterion in F2. The second column of that range is the column to filter.

Sub FilterByCellValue()
    Dim ws As Worksheet
    Dim filterRange As Range
    Dim criteriaCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set filterRange = ws.Range("A1:D100")
    Set criteriaCell = ws.Range("F2")

    ' Stop rather than filter for an empty selection.
    If Len(CStr(criteriaCell.Value)) = 0 Then
        MsgBox "Enter a filter value in F2."
        Exit Sub
    End If

    ' Clear the current sheet filter before applying the new one.
    If ws.FilterMode Then ws.ShowAllData

    filterRange.AutoFilter Field:=2, Criteria1:=criteriaCell.Value
End Sub

Change the worksheet name, range, cell, and field number to fit the workbook. The empty-cell check is a deliberate choice: remove it if a blank in the control cell should filter for blank entries instead.

How to set up and run the macro

  1. Arrange the data as a single rectangular range with one header row and no completely blank row splitting the records. Put the value to match in a separate cell, such as F2.
  2. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, then paste the procedure into the module. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
  3. Update the worksheet name and range in the code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project is retained.
  4. Return to Excel and run the macro from the Visual Basic Editor, or assign it to a button. To use a button, add a Form Control button from Developer > Insert and select the macro when prompted.

Excel for Mac also supports VBA in the desktop application, although ribbon and keyboard details can vary with the keyboard and version. The Visual Basic Editor is available from the Developer tab; on many Mac keyboards Option+F11 opens it, and some keyboards also require Fn. Excel for the web cannot run VBA macros, so use desktop Excel to execute this code. Before running any macro, save a separate copy of the workbook: macro actions cannot be reversed with Ctrl+Z.

Choosing the correct range and Field number

A frequent mistake is treating Field as an absolute worksheet column number. It is relative to the left edge of the range passed to AutoFilter. If the filtered range is A1:D100, A is Field 1, B is Field 2, and D is Field 4. If the range instead begins in column C, then C is Field 1 and D is Field 2. Including the header row is important because Excel uses it to identify the columns.

For changing datasets, a fixed endpoint such as row 100 may omit new records. One option is to convert the data to an Excel Table using Insert > Table. Tables expand as rows are added and provide named columns, making the filter range less fragile. For a table named Sales, the following macro filters its Region column using the value in F2:

Sub FilterSalesTableByCell()
    Dim ws As Worksheet
    Dim salesTable As ListObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set salesTable = ws.ListObjects("Sales")

    ' The table column index is relative to the table.
    salesTable.Range.AutoFilter _
        Field:=salesTable.ListColumns("Region").Index, _
        Criteria1:=ws.Range("F2").Value
End Sub

Replace Sales and Region with the actual table and column names. This approach is often clearer than counting columns by hand, but it depends on the table and column names remaining correct.

Text, numbers, dates, blanks, and wildcard criteria

For an ordinary text criterion, the cell’s value is usually passed directly. AutoFilter compares it with entries in the selected field; a criterion such as West is not the same as East. Leading or trailing spaces in either the control cell or data can cause a seemingly correct filter to return no rows. If appropriate, clean the source data or trim the entered value before filtering.

Numeric cells should contain actual numbers when the data column contains numbers. A number stored as text may not behave like a numeric value. Dates deserve the same care: Excel stores genuine dates as serial numbers with date formatting, while text that merely looks like a date is still text. Confirm that the criterion cell and the source column use compatible underlying values and formats before concluding that the macro is broken.

AutoFilter supports criteria such as greater than or equal to a number, and wildcard matching for text. For instance, a criterion beginning with >= can select numeric values at or above a threshold; * represents any sequence of characters and ? represents one character in wildcard criteria. An exact match can be forced with an equals sign when a value might otherwise be interpreted as a pattern. Excel’s filter interface and VBA criteria rules have special cases for blank values and dates, so test those against a small copy of the data before applying them to a report.

If the control cell is meant to select blank records, do not use the empty-cell guard in the first example. AutoFilter commonly uses the criterion ="" to target blank cells, but an empty string and a cell that contains a formula returning an empty string are not always treated identically in every filtering scenario. Test the actual source data and decide whether the control cell means “show blanks” or “no selection.”

Keeping the filter in sync with the cell

A standard macro runs only when invoked. If the user changes F2, the displayed rows remain filtered by the old value until the macro runs again. For a simple workflow, a button labelled Apply Filter is often preferable: it makes the action explicit and avoids running code on every edit.

To apply the filter automatically, a Worksheet_Change event can respond to edits in the control cell. Event code belongs in the worksheet’s code module, not a standard module. It should call a separately maintained filtering procedure or contain carefully limited filtering logic. An event handler must also avoid triggering itself repeatedly if it changes worksheet cells; VBA commonly uses Application.EnableEvents = False around code that edits cells, then restores events even if an error occurs. Because event procedures are easier to disrupt than a button macro, use them only when automatic updating is genuinely useful and test edits, pastes, and clearing the control cell.

Clearing filters without disturbing the sheet

The example uses If ws.FilterMode Then ws.ShowAllData before applying a new criterion. This shows all rows currently hidden by filters on that worksheet, after which the new condition is applied. It may also clear other active filters on the same sheet, not just the target column’s filter. If users rely on several simultaneous filters, clearing all of them may be inconvenient.

AutoFilter criteria on different fields normally combine: a row must satisfy each active field’s condition to remain visible. If a macro should replace only one field’s criterion while preserving others, avoid calling ShowAllData indiscriminately. Apply or replace the target field’s filter and test the result with the other fields already filtered. If the goal is to remove all filtering, use the filter controls deliberately; hiding the filter arrows and showing all rows are separate actions.

Common problems and practical checks

  • No rows appear: Check that the criterion actually exists in the chosen field, that it has no unwanted spaces, and that the field number points to the intended column.
  • The wrong column is filtered: Count from the leftmost column of the range passed to AutoFilter, or use a table column’s index as in the second example.
  • The macro reports a subscript error: Confirm that the worksheet name and table name match exactly, including spaces.
  • New records are not included: Expand the range or use a Table that grows with appended rows.
  • The macro does not run: Confirm that the file is saved as .xlsm and that macros are permitted by the organization’s security settings. Do not enable macros in files from an untrusted source.
  • Filters disappear unexpectedly: Review whether the macro calls ShowAllData, which clears active filters on that worksheet.

Also check for merged cells, duplicate or missing headers, and data stored outside the intended rectangle. A reliable filter range has one header row, a consistent set of columns, and records directly beneath the headers. For a report that must preserve user-applied filters, test the macro on a copy with multiple fields filtered before deploying it.

When VBA is not the best option

If the goal is simply to filter data interactively, Excel’s built-in Data > Filter controls may be enough; no macro is needed. A Table’s filter arrows also let a user select values directly. If the output needs to appear in a separate area and update from a cell, a formula-based approach may be more suitable in supported versions of Excel, but it produces a result range rather than hiding rows in the original dataset.

VBA is a good fit when a button, repeatable reporting step, or integration with other workbook actions is required. It is less suitable when the file must work in Excel for the web, when macros are blocked by policy, or when a formula or built-in filter can meet the need more simply. Choose based on whether the requirement is to hide rows in place or generate a separate filtered result.

Frequently Asked Questions

How do I filter a column using a value in another cell?

Call AutoFilter on the range containing the header and data, set Field to the column’s position within that range, and set Criteria1 to the other cell’s value. For example, filterRange.AutoFilter Field:=2, Criteria1:=Range("F2").Value uses F2 as the criterion for the second field.

Does changing the cell update the filter automatically?

No. A regular macro applies the value present when the macro runs. Run it again after changing the cell, or use a worksheet change event if automatic filtering is necessary.

Can I use a cell value with an Excel Table?

Yes. Call AutoFilter on the table’s range and use the table column’s index for Field. Tables are convenient because they expand as data is added, and their named columns make the intended field easier to identify.

Why does Field:=2 filter the wrong column?

Field is counted from the first column of the range supplied to AutoFilter, not from worksheet column A. If your range starts at C, Field 1 means C and Field 2 means D.

Can I run Excel VBA AutoFilter in a browser?

No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the VBA procedure.

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.