Excel VBA AutoFilter Tables: Setup, Code, and Common Fixes

VBA & macros · Updated November 21, 2026

To filter an Excel table with VBA, use the table’s Range.AutoFilter method and specify the column’s position within the table and the value to match. For example, a macro can filter a table named tblSales so only rows whose Status is Open remain visible; the key is to address the table by name rather than assume the data starts in a particular worksheet column.

How AutoFilter works with an Excel table

An Excel table, also called a ListObject in VBA, already has filter drop-downs in its header row by default. VBA does not need to create a separate filter range or include the header row manually. The table’s Range.AutoFilter method applies criteria to one of its columns, while Excel keeps the table structure, header, and formatting in place.

Filtering hides rows that do not meet the criteria; it does not delete them or change their values. The table’s totals row, if enabled, and formulas that respond to visible rows may behave according to their own settings, but the underlying records remain in the table. A filter can be applied through the ribbon, a table header drop-down, or VBA, and Excel reflects the same active filter state across these methods.

For a table named tblSales with headers Region, Status, and Amount, the Status field is column 2 within the table. That remains true even if the table begins in worksheet column D: the Field argument counts from the table’s left edge, not from worksheet column A.

Set up the table and macro

Before writing the procedure, make sure the data is a real Excel table, not merely a formatted range. Click in the data and use Insert > Table, confirm that the header row is included, and give the table and worksheet clear names. With a cell in the table selected, open Table Design and use the Table Name box to set a name such as tblSales. Avoid spaces in table names.

To add VBA, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the procedure into the standard module. On a Mac, use Option+F11 (on some keyboards, Fn+Option+F11) to open the editor; keyboard and function-key behavior can vary by Mac settings. Excel for the web can open workbooks containing macros, but it cannot create, edit, or run VBA macros, so use the desktop application.

The example assumes a worksheet named Sales, a table named tblSales, and a header named Status. Change those names to match the workbook exactly:

Sub FilterSalesByStatus()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim statusField As Long

    ' Point to the worksheet and table by their names.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set tbl = ws.ListObjects("tblSales")

    ' Find Status by header name, then filter for Open.
    statusField = tbl.ListColumns("Status").Index
    tbl.Range.AutoFilter Field:=statusField, Criteria1:="Open"
End Sub

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, and macros can change workbook state in ways that are not covered by Excel’s normal undo history. Save a macro-enabled workbook as an .xlsm file if you need to retain the code.

Why look up the field by its header?

You can supply a fixed number, such as Field:=2, when the table’s column order will not change. Looking up the column by name is usually more robust: if someone inserts a new table column before Status, the macro still finds the Status field. The ListColumns("Status").Index expression returns the field’s current position within that particular table.

Header spelling matters. A header named “Order Status” is not the same as “Status,” and a missing or renamed header causes a VBA error when the code tries to retrieve it. If a workbook is maintained by other people, consider checking whether the worksheet, table, and column exist before filtering, and show a useful message if any name has changed. That check is especially valuable in larger automation routines, but the short example keeps the core operation easy to see.

For a quick one-off macro, a numeric field index is acceptable when you control the table layout. For a reusable macro or a workbook where columns may move, using the header name makes the intention clearer and reduces silent mistakes.

Filter by text, multiple values, or a condition

The simplest criterion matches a value in one table column. For example, to show only rows where Region is West, use the Region field’s index and set Criteria1:="West". Text criteria are generally matched without regard to letter case in Excel’s standard AutoFilter behavior. A value that is not present in the column produces no matching data rows; it does not create a new row or report that the value is invalid.

To show more than one exact text value in the same column, pass an array and use xlFilterValues. In the Sales example, this filters Status to Open or Pending:

Sub FilterSalesToOpenOrPending()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim statusField As Long

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set tbl = ws.ListObjects("tblSales")
    statusField = tbl.ListColumns("Status").Index

    ' Keep rows whose Status is either value in the array.
    tbl.Range.AutoFilter Field:=statusField, _
        Criteria1:=Array("Open", "Pending"), _
        Operator:=xlFilterValues
End Sub

For a comparison criterion, use a comparison operator as part of the criterion string. For instance, a numeric Amount column can be filtered for values greater than 100 with Criteria1:=">100". Ensure that the source values are actually numeric; numbers stored as text, blanks, and mixed data types can lead to results that do not match what a reader expects. AutoFilter also supports two criteria on one field with an operator such as xlAnd or xlOr, but multi-select lists and comparison pairs are different filtering patterns and should not be mixed without checking the desired result.

Wildcards can help with text searches: an asterisk represents any number of characters, while a question mark represents one character. A criterion such as *north* matches text containing “north.” To search for a literal wildcard character, Excel has special handling using a tilde; test the exact pattern against representative data, particularly when the column contains punctuation or inconsistent spacing.

Filter more than one table column

Applying a filter to another field does not normally replace the first field’s filter. Excel combines active filters across columns, so the visible records must meet the criteria in both fields. For example, filter Status to Open, then Region to West, and the table shows records that are both Open and in the West region. This is an AND relationship across the separately filtered columns.

A macro can apply both criteria by calling AutoFilter once for each field. Determine each field index from its header name, just as the Status example does, then make a second call using the Region field and its criterion. This is often easier to maintain than building one complicated filter expression, because each line expresses one condition. Be aware that a later macro may leave old criteria active if it changes only one column; decide whether the procedure should preserve existing filters or deliberately reset them first.

When the result appears unexpectedly empty, inspect every filtered column, not just the field your macro changed. A previous filter, hidden rows, or a criterion that does not match the actual cell contents can explain why no records are visible.

Clear filters without deleting records

Clearing a filter reveals rows again; it does not remove data. A common way to show all rows currently hidden by filters on a worksheet is to test FilterMode and call the worksheet’s ShowAllData method:

Sub ShowAllRowsOnSalesSheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Show all rows if this worksheet currently has an active filter.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

Use this with care: ShowAllData applies to the worksheet, so if that sheet has other filtered ranges or tables, their active filters may also be cleared. It does not turn off the table’s header drop-downs. If preserving unrelated filters is important, design the procedure to manage only the intended table and test the behavior in a copy of the workbook before using it on production data. Avoid confusing clearing filter criteria with hiding the drop-down arrows; those are separate tasks.

Common AutoFilter problems and fixes

  • Run-time error for a missing table: Confirm the worksheet name and the Table Name box value. Worksheet tab names and table names are separate identifiers.
  • Run-time error for a missing column: Check the exact header text, including spaces. If users can rename headers, validate the column name before using ListColumns.
  • The wrong column is filtered: Remember that Field starts at 1 for the table’s leftmost column. It is not the worksheet column number.
  • No rows appear: Check whether another column already has a filter and confirm that the criterion matches the stored values. Leading or trailing spaces and numbers stored as text are common sources of surprises.
  • Only part of the data is filtered: Verify that all records belong to the named table. Data pasted immediately below a table is not always part of it until the table expands or the range is resized.
  • The macro does not run: Confirm that macros are permitted by the workbook’s security settings and that the file is open in desktop Excel. Excel for the web does not execute VBA.

Do not use ActiveSheet as a shortcut in a macro that might run while another sheet is selected. Explicitly setting the worksheet and table makes the code predictable. Likewise, avoid relying on a table’s current worksheet position: named ListObjects and header lookups remain meaningful when the layout changes.

Tables versus ordinary filtered ranges

AutoFilter can also be applied to a normal range, but a table is often the better target for a growing list of records. Tables have a name, structured columns, and behavior that can extend when data is added; their filters are tied to the table rather than an assumed fixed address. This helps avoid code that filters only rows 1 through 200 after the list has grown to 500.

A regular range can be appropriate for legacy layouts, reports with non-tabular structures, or cases where converting the data to a table would disrupt existing workbook design. In that case, the macro must correctly identify the full range and its header row. Do not apply a table example unchanged to an ordinary range: a table is addressed through the worksheet’s ListObjects collection, while a normal range has no ListObject name.

For a simple interactive task, the table’s built-in header arrows may be enough. VBA is useful when the filter needs to be repeatable, run from a button, or coordinated with other workbook steps. If the goal is to calculate a dynamic subset in cells rather than hide source rows, Excel’s worksheet functions may be a better fit. The FILTER function is available in Microsoft 365 and Excel 2021 or later, but it returns a formula result rather than applying a table’s interactive AutoFilter state.

Frequently Asked Questions

Does an Excel table need AutoFilter turned on before VBA can filter it?
Tables normally display filter arrows in their header row by default. The table’s Range.AutoFilter method applies the criteria to the table. If the arrows have been hidden or the table settings have changed, check the table’s filter controls and confirm that the object is still an Excel table.

What does the Field argument mean in VBA AutoFilter?
Field is a one-based position within the filtered range or table. For a table whose columns are Region, Status, and Amount, Region is field 1, Status is field 2, and Amount is field 3, regardless of where the table starts on the worksheet.

Can VBA filter a table using a column name instead of a number?
The AutoFilter method takes a field position, but VBA can get that position from a column name. Use the index of the table’s ListColumn with the desired header, as in tbl.ListColumns("Status").Index, and pass that number to Field.

Does AutoFilter delete rows that do not match?
No. It hides nonmatching rows from view. Clearing the criteria makes them visible again; deleting records requires a separate deletion operation and should not be added casually to a filter macro.

Can I run an Excel VBA AutoFilter macro on a Mac or in Excel for the web?
VBA is available in the desktop Excel app for Windows and Mac, though editor shortcuts can differ. Excel for the web can display workbook data but cannot run VBA macros; open the file in desktop Excel to execute the code.

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.