Excel Macro Filter Table: VBA Code, Steps, and Tips

VBA & macros · Updated November 19, 2026

To filter an Excel table with a macro, use VBA’s AutoFilter method on the table and specify the column and criteria. For example, a macro can show only rows where the table’s Region column equals West; the code below explains how to set that up, change the criteria, and clear the filter.

What filtering an Excel table means

An Excel table is a structured range with headers and built-in features such as sorting, filter buttons, and automatic expansion when new rows are added. A macro can apply a filter to that table without selecting cells or relying on fixed worksheet row numbers. This makes a table filter more resilient than a macro that targets a range such as A1:F500: the table’s range can grow as records are added.

Filtering hides rows that do not match the selected criteria; it does not delete those records. The table’s filter arrows remain available, and users can still change the filter manually. A macro is useful when the same view is needed repeatedly, when a button should apply a standard filter, or when a larger process needs to filter a table before doing other work.

The example in this article assumes a worksheet named Data, an Excel table named SalesTable, and a header named Region. Excel table names and column headers must match the names in the VBA code. Table names do not contain spaces, although headers can.

Check the workbook and table first

Before adding VBA, confirm that the data is formatted as an Excel table and that its header row is clear. Click inside the data and look for the Table Design tab. If the range is not a table, select it and choose Insert > Table, or press Ctrl+T on Windows, then confirm whether the range has headers. On Mac, use Insert > Table; shortcut behavior can vary with keyboard settings.

Give the table a useful name. Click in the table, open Table Design, and edit the Table Name box, typically at the left of the ribbon. A name such as SalesTable is easier to recognize in code than the default Table1. Check the worksheet tab name and header spelling too; VBA will return an error if a referenced worksheet, table, or column cannot be found.

Save the workbook in a format that can store VBA: Excel Macro-Enabled Workbook (*.xlsm). Saving a macro-enabled file as a regular .xlsx workbook removes its VBA project. A copy of the workbook is a sensible starting point, especially if the macro will be used on important data.

Add and run a basic filter macro

On Windows, show the Developer tab if it is not visible by going to File > Options > Customize Ribbon and checking Developer under Main Tabs. Choose Developer > Visual Basic, then in the editor choose Insert > Module. On Mac, enable Developer from Excel > Settings > Ribbon & Toolbar, then choose Developer > Visual Basic. The VBA editor can also be opened with Alt+F11 on Windows; on Mac, the equivalent is usually Option+F11, sometimes with Fn as well depending on the keyboard.

Paste this procedure into the module. It filters the Region column to show West:

Sub FilterSalesToWest()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim fieldIndex As Long

    ' Identify the worksheet and Excel table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    ' Find Region's position within the table.
    fieldIndex = tbl.ListColumns("Region").Index

    ' Show rows where Region is West.
    tbl.Range.AutoFilter Field:=fieldIndex, Criteria1:="West"
End Sub

In the code, ThisWorkbook means the workbook that contains the macro. The field index is looked up by header name, rather than being typed as a number, so the procedure still targets Region if that column moves within the table. The Field argument is the column’s position inside the table, not its worksheet column number. The criteria string is an exact text match here.

To run it, return to Excel and choose Developer > Macros, select FilterSalesToWest, and click Run. You can also run the procedure from the VBA editor. The result is the same as selecting West from the Region filter menu, but the macro makes the operation repeatable.

Change the criteria and filter more than one column

For another exact text filter, change Criteria1:="West" to a value that exists in the column, such as Criteria1:="East". To filter a different field, look up that table column instead. For instance, changing the header name to Product and the criterion to "Notebook" filters the Product column. The header text must match the table header exactly, including spaces.

AutoFilter also accepts comparison criteria. For example, to find amounts from 100 up to but not including 500 in a numeric Amount column, use this line after setting the corresponding field index:

tbl.Range.AutoFilter Field:=fieldIndex, Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<500"

The two criteria in that example apply to the same field, and xlAnd requires both conditions to be true. For two alternatives in one field, such as West or East, use Operator:=xlOr with the two text criteria. If you need filters on separate columns, call AutoFilter once for each field; for example, filter Region first and Product next. A filter applied to another field normally leaves the existing field filter in place, so the visible rows must meet both columns’ conditions.

Text criteria can use wildcards: an asterisk represents any number of characters, and a question mark represents one character. For example, Criteria1:="North*" can match entries beginning with North. A tilde is used to treat a wildcard character as a literal character. Date filters deserve extra care because dates may be displayed differently by region or stored as text instead of real Excel dates. Verify the column’s underlying values and test the macro on a copy before relying on a date criterion.

Clear a table filter or replace it

Applying a new criterion to the same field replaces that field’s current criterion. To show every row in the table again while keeping the filter buttons, use a separate procedure. It checks whether the table is filtered before calling ShowAllData:

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

    ' Identify the table whose filters should be cleared.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    ' Restore all rows if this table currently has an active filter.
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    End If
End Sub

This clears the active filtering on that table; it does not delete records or remove the table. Be deliberate about scope: ws.ShowAllData is a worksheet-level operation and can affect filtered data elsewhere on the same sheet. Use the table’s AutoFilter when the intention is to clear only this table’s filter.

If the macro should switch between views, it can apply the new criterion directly rather than clearing first. If a control should restore all records, call the clear procedure. A button can be assigned to either macro by choosing Developer > Insert, adding a Form Control button, and selecting the procedure when prompted. Button placement and labels are optional; the macro itself works without a button.

Common problems and how to fix them

  • “Subscript out of range”: Check the worksheet name and table name in the code. Worksheet tabs and table names are separate things; changing one does not rename the other.
  • “Subscript out of range” for a column: Check the header spelling, punctuation, spaces, and any hidden trailing spaces. The code refers to the table header, not a letter such as column C.
  • The wrong field is filtered: Make sure the field index comes from tbl.ListColumns("Header").Index. A worksheet column number is not necessarily the same as the table’s field number, particularly if the table starts away from column A.
  • The macro runs but shows no rows: Check that the criterion matches the data. Text with an extra space, numbers stored as text, or dates stored as text can prevent an apparent match. Try the criterion manually in the table’s filter menu to diagnose the data.
  • The macro is missing or blocked: Confirm the file is saved as .xlsm and that macros are permitted by your organization’s security policy. Do not assume a missing Developer tab means the code was lost.
  • New rows are not included: Check that the new records were added to the table rather than merely placed below it. Table filters apply to table rows, not automatically to unrelated cells beneath the table.

Also check that the worksheet is not protected in a way that prevents filtering. Protection settings can limit actions even when the VBA code is correct. If a workbook is shared or used by colleagues, test the macro under the same protection and access conditions they will have.

When to use a macro instead of a manual filter

For occasional exploration, the table’s built-in filter arrows are usually simpler: choose a column’s arrow, select or search for values, and apply the filter. No VBA or macro-enabled file is required. A macro is more appropriate when users need a consistent one-click view, when a procedure must repeat as part of a workflow, or when someone may otherwise select the wrong criteria.

A macro is not automatically the best choice for every recurring view. If the goal is to display a changing analysis on a separate sheet, a PivotTable or a dynamic-array formula may be more suitable, depending on the workbook and Excel version. If the task is just filtering one table by hand, a macro adds maintenance and security considerations without much benefit. Consider who will open the file, whether they can run VBA, and how often the filter needs to be repeated.

Macro safety and Excel platform differences

VBA macros run in the desktop versions of Excel for Windows and Mac, subject to security settings. Excel for the web can open and edit many workbooks, but it cannot create, run, or edit VBA macros. A recipient who needs to run the macro should use desktop Excel and receive the macro-enabled workbook.

On Windows, macro security settings are in File > Options > Trust Center > Trust Center Settings > Macro Settings. On Mac, macro security options are under Excel > Settings > Security. Organization policies may control these settings. Only run macros from sources you trust; avoid changing settings to enable all macros just to run an unfamiliar file. If a workbook comes from the internet, follow your organization’s guidance or ask its support team before enabling its content.

Keep a backup before running or editing VBA. Macro actions generally cannot be reversed with Ctrl+Z, so a mistaken procedure may require restoring a saved copy. Ctrl+Z is the standard Windows undo shortcut; on Mac, Command+Z is the equivalent, but it does not make VBA actions undoable. Test with a small or copied dataset, especially before adapting the code to delete, overwrite, or export records.

Frequently Asked Questions

Can an Excel macro filter a table by a cell’s value?

Yes. A macro can read a worksheet cell and pass its value as the AutoFilter criterion. This is useful when a user selects a region or status from a control cell. The code should still identify the table and its column explicitly, and it should handle blank or invalid selections if those are possible.

Does filtering with VBA delete rows that do not match?

No. AutoFilter hides nonmatching rows; it does not remove them. Deleting filtered rows requires separate VBA instructions and carries a different risk, so do not add deletion code unless that is specifically intended and tested.

Can I filter two columns at the same time?

Yes. Apply AutoFilter to each table field in turn, using the appropriate field index and criterion. Excel then shows rows that satisfy the active filters across the columns. Two criteria on one field use the AutoFilter operator options, while filters on separate fields are separate calls.

Why does my macro say the table or column is not found?

The worksheet name, table name, or header in the code does not match the workbook. Check the Table Design tab for the actual table name and compare the header character by character, including spaces. Also confirm that the macro is running from the workbook that contains the expected table.

Can Excel for the web run a VBA filter macro?

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 procedure, subject to the applicable macro security settings.

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.