Excel VBA AutoFilter Example: Code and Tips

VBA & macros · Updated November 21, 2026

To filter a worksheet with VBA, use the AutoFilter method on a range that includes its header row. For example, if column C is named Status, this macro shows rows whose status is Open: dataRange.AutoFilter Field:=3, Criteria1:="Open".

A complete Excel VBA AutoFilter example

This example assumes an Orders worksheet with headers in row 1, records below them, and Status as the third column in the data. The macro finds the contiguous data block around A1, clears an existing filter if necessary, and filters the Status field to Open.

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

    ' Use the worksheet and data block that contain the table.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Remove current criteria before applying the new one.
    If ws.FilterMode Then ws.ShowAllData

    ' Field 3 means the third column in dataRange, not worksheet column C in every case.
    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, and this macro changes which rows are visible. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code.

How AutoFilter works

AutoFilter adds dropdown controls to a header row and hides records that do not match the selected criteria. It does not delete the excluded rows or move matching records to a new location. In VBA, the Range.AutoFilter method applies a filter to a range, while Excel keeps the underlying values in place.

The filter range should include the headers as well as the data rows. In the example, CurrentRegion finds the connected block around A1, stopping at completely blank rows or columns. That is convenient for a simple, uninterrupted list. If a blank column splits the data, or the worksheet has unrelated content directly beside the table, use an explicitly defined range or an Excel Table instead.

The Field argument is a one-based position within the filtered range. If the range begins at A1, Field 3 refers to column C. If it begins at D1, Field 3 refers to column F. This relative numbering is a frequent source of errors when a macro filters a range that does not start in column A.

Set up the workbook and run the macro

  1. Arrange the records in a single block with one header row. Use distinct, nonblank column headings, and avoid merged cells in the header.
  2. Press Alt+F11 on Windows to open the Visual Basic Editor. Choose Insert > Module, then paste the procedure into the module.
  3. Change the worksheet name, starting cell, field number, and criterion to match the workbook. The worksheet name in the code must match the tab name.
  4. Return to Excel and run the macro from the Visual Basic Editor, or use Developer > Macros, select FilterOpenOrders, and choose Run.

If the Developer tab is not visible in Excel for Microsoft 365 on Windows, enable it through File > Options > Customize Ribbon by selecting Developer. On a Mac, VBA editing is available in desktop Excel, but ribbon and settings locations differ; Excel for the web cannot create or run VBA macros. A macro-enabled workbook can still be opened in the web app, but its VBA code does not execute there.

Change the criteria for text, numbers, and multiple values

Change Criteria1 to match the values in the column. Text criteria are normally written as strings, and comparisons for numeric fields use comparison operators. The following examples assume the same data range and field numbering as the main macro:

  • For an exact text match, use Criteria1:="Closed".
  • For a numeric threshold, use Criteria1:=">=1000" to show values of 1,000 or more.
  • To show text values that begin with North, use the wildcard criterion Criteria1:="North*". An asterisk matches any number of characters; a question mark matches one character.
  • To show either Open or Pending in the same field, use Criteria1:=Array("Open", "Pending"), Operator:=xlFilterValues.

Criteria text must suit the actual cell contents. A value that looks like a date or number may be stored as text, in which case a numeric or date comparison may not behave as expected. For date filtering, verify that the cells contain real Excel dates and consider regional date settings when constructing criteria in code. Test a date filter on a copy if the workbook is used with different locale settings.

AutoFilter can also combine criteria from different fields. For example, a filter can show rows where Status is Open and Region is West by applying the method to the range once for each field, with the relevant field number and criterion. Each additional field narrows the visible results, because records must meet all the active field filters.

Use an Excel Table for a more resilient range

A worksheet range is often enough for a short list, but an Excel Table is usually easier to maintain as records are added. Tables expand when users enter data immediately below them, and their filter range stays associated with the table. This avoids relying on CurrentRegion when nearby data or blank lines could change the detected block.

If a table is named OrdersTable, a macro can refer to it directly. In this example, the table has a column named Status:

Sub FilterOrdersTable()
    Dim ws As Worksheet
    Dim orders As ListObject
    Dim statusField As Long

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set orders = ws.ListObjects("OrdersTable")
    statusField = orders.ListColumns("Status").Index

    If ws.FilterMode Then ws.ShowAllData
    orders.Range.AutoFilter Field:=statusField, Criteria1:="Open"
End Sub

Using the column name to find its index is safer than hard-coding a number if the table’s columns may be rearranged. The index is still relative to the table. Create or rename a table from the Table Design tab after selecting it; the table name appears in the Properties group.

Clear, replace, or inspect filters

Applying a new filter does not always mean every existing filter on the sheet is cleared. In the examples, If ws.FilterMode Then ws.ShowAllData first reveals all rows currently hidden by filter criteria, then applies the new condition. The check matters because calling ShowAllData when no filter is active can raise an error.

To remove filtering completely, rather than simply show all records, use the worksheet’s AutoFilter controls or turn off the filter for the range. To show all rows but keep the dropdown arrows, use ShowAllData when a filter is active. These are different outcomes: clearing criteria preserves the filtering interface, while turning AutoFilter off removes it.

When a workbook has several independent filtered ranges, be precise about which worksheet and range the code targets. A macro that clears filters across an entire sheet may affect a user’s other work, so avoid broad cleanup unless that is the intended behavior.

Common problems and how to fix them

  • Run-time error when naming the sheet: Check the tab name for spelling and spaces. ThisWorkbook refers to the workbook containing the macro; use a different workbook reference only when the data is intentionally elsewhere.
  • The wrong column is filtered: Recount Field from the left edge of the filter range, starting at 1. It is not always the same as the worksheet’s column number.
  • Only some new rows appear: Confirm that the range includes all records. A blank row or column can make CurrentRegion stop early; a table or explicit range can help.
  • No records match: Check for trailing spaces, inconsistent spelling, or values stored as text rather than numbers. Filter criteria generally need to match the underlying values, not just the way they look on screen.
  • Dropdown arrows are missing: Confirm that the method was applied to a range containing a header row and that the sheet is not protected in a way that prevents filtering.
  • The macro will not run: Confirm the workbook is saved in a macro-enabled format and that the code is in a standard module. Only enable macros in files you trust; your organization may also control macro security settings.

AutoFilter versus sorting and worksheet filters

Filtering and sorting solve different problems. A filter hides rows that fail a condition; sorting rearranges the order of rows. You can use both on the same list, but a filter does not sort the matching records automatically. If the goal is to create a separate report containing only matches, a VBA filter may not be the best tool: it changes the visible state of the source list rather than copying results elsewhere.

For a one-time task, Excel’s built-in dropdowns may be simpler than a macro. Select a cell in the list and use Data > Filter, then choose a condition from the relevant dropdown. VBA is useful when the same filtering action must be repeated, attached to a button, or included in a larger automation. A formula-based approach can be better when results should update in a separate area without hiding source rows; the FILTER worksheet function is available in Microsoft 365 and Excel 2021 or later.

Frequently Asked Questions

Does AutoFilter delete rows that do not match?
No. It hides nonmatching rows in the filtered range. The values remain in the worksheet and can be shown again by clearing the filter.

What does Field:=3 mean in VBA AutoFilter?
It means the third column within the range passed to AutoFilter. Count from the range’s leftmost column, not necessarily from column A on the worksheet.

How do I filter for more than one value?
For several acceptable values in one field, pass an array in Criteria1 and set Operator:=xlFilterValues. The values should match entries in the chosen column.

Can VBA AutoFilter work with an Excel Table?
Yes. Apply AutoFilter to the table’s Range, or use table column names to determine the field index. Tables are helpful when the data grows over time.

Can I run an AutoFilter macro in Excel for the web?
No. Excel for the web can display a workbook containing VBA, but it cannot run VBA macros. Use the desktop version of Excel to run 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.