VBA AutoFilter: What It Filters and How to Use It

VBA & macros · Updated November 18, 2026

VBA AutoFilter filters the rows in an Excel list or table according to criteria in one or more columns, hiding rows that do not match. It does not delete those rows or change their values; they remain in the worksheet and can be shown again by clearing the filter.

What VBA AutoFilter can filter

AutoFilter works on a rectangular data range with a header row, or on an Excel table. Each column can have a dropdown filter, and VBA can apply criteria through those same column fields. Common uses include showing orders for one customer, products below a stock threshold, records from a date range, or entries whose status is still Open.

For example, a sales list might have the headings Region, Date, Amount, and Status. A filter can show only rows where Region is West, or only rows where Amount is greater than 500. Criteria can also be applied to more than one field at once; a row remains visible only if it satisfies the active filters.

Filtering is a view of the data, not a separate result set. Hidden rows are still part of the worksheet, so formulas and other VBA procedures may still encounter them unless those procedures specifically work with visible cells. If you need a permanent subset, copy the visible records to another range after filtering rather than assuming the filter removed anything.

How AutoFilter works in Excel

An AutoFilter range has one header row followed by records. Excel adds dropdown arrows to the headers and uses the header names to offer relevant filter choices. In VBA, the AutoFilter method applies a filter to a range. Its Field argument identifies the column to filter, and Criteria1 and, when needed, Criteria2 specify what values to keep.

The field number is counted from the left edge of the range supplied to AutoFilter, not from worksheet column A. If the range is A1:D100, Field 1 means column A and Field 3 means column C. If the range instead starts at C1, Field 1 means column C. Getting this distinction wrong is a frequent reason a macro filters the wrong column.

Excel evaluates the criteria against the values in the selected field, then hides complete data rows that fail the filter. When several fields have filters, the conditions work together: a row must pass every active field filter to remain visible. The row numbers may appear to skip, but the hidden records have not been removed.

Common criteria and examples

Criteria depend on the kind of data in the column. Text criteria can match a value such as West; numeric criteria can use comparison operators; and wildcard characters can match text patterns. A criteria string beginning with an operator is interpreted as a comparison, for example greater than or equal to a threshold.

  • Exact text: use a value such as Open to keep rows with that status.
  • Numbers: use criteria such as >500 or <=100 for numeric comparisons.
  • Text patterns: an asterisk represents any number of characters, while a question mark represents one character. Use a tilde to treat a wildcard character literally.
  • Blanks: blank-value filters are useful for finding incomplete records, but cells containing formulas that return an empty string may not behave exactly like genuinely empty cells in every filtering context.

For dates, Excel stores dates as serial values, but the text used for a date criterion can be affected by regional date settings. If a date filter behaves unexpectedly, check the cell values are real Excel dates rather than text and avoid assuming that a month/day string has the same interpretation on every computer. For complex date grouping, test the macro against representative data.

A basic VBA AutoFilter example

This example assumes the worksheet has headers in A1:D1, records below them, and a Status field in column C. The data range must include the header and all records to be filtered. Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z.

Option Explicit

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

    ' Use the active sheet and include the header row.
    Set ws = ActiveSheet
    Set dataRange = ws.Range("A1:D100")

    ' Remove an existing filtered view before applying this example.
    If ws.FilterMode Then ws.ShowAllData

    ' Field 3 is column C within A1:D100.
    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

The example keeps rows whose third field equals Open. The fixed endpoint, row 100, is only appropriate when the data really ends there. For changing lists, consider using an Excel table or determining the final data row so new records are included. Also make sure the range contains a single header row and does not accidentally include unrelated cells beside the list.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. On Mac, the Visual Basic Editor shortcut differs by keyboard and system settings, and Microsoft documents limitations for the Range.AutoFilter method; the VBA code above targets Windows. Excel’s worksheet filter controls are available through the interface, but Mac users should confirm method support for their specific object and version before adapting a macro.

Filtering more than one column

Apply AutoFilter to the same data range again with a different Field number to add another field condition. For example, after filtering Status to Open, filtering Field 1 to West keeps only open records in the West region. The second call does not necessarily replace the first field’s condition; each field has its own dropdown filter.

For two acceptable values within one field, AutoFilter can use two criteria and an operator. For instance, Criteria1:="West", Operator:=xlOr, Criteria2:="East" expresses a choice between two values in the same field. By contrast, applying a separate Region filter and Status filter combines the fields, so both conditions must be met. The distinction between OR within a field and AND across fields is useful when translating a user’s request into code.

AutoFilter also supports specialized criteria, including date groupings and top or bottom values, but those options require the appropriate operator and criteria structure. If criteria are assembled from worksheet cells or user input, validate them and test cases such as empty inputs, numbers stored as text, and values containing wildcard characters. An invalid or mismatched criterion may return no visible records without producing an obvious error.

Clearing filters and managing the filtered range

To show all records while leaving the dropdown arrows in place, use the worksheet’s Clear command in the Sort & Filter group or call ShowAllData when the sheet is actually in a filtered state. In the code above, the FilterMode check prevents calling ShowAllData when there is no active filter. This matters because calling it without an active filtered view can raise an error.

To remove the AutoFilter arrows as well as the filter setup, toggle AutoFilter for the range or use the Data tab’s Filter control. Be careful: calling AutoFilter without criteria can toggle the dropdowns on or off, so a macro should not use it casually if its purpose is only to clear the current criteria. If the list is an Excel table, its filter controls belong to the table, and using the table’s range or ListObject methods can make code clearer than relying on a hard-coded worksheet address.

  • Keep the header row inside the target range so Excel assigns filters to the intended fields.
  • Use a table when the list grows over time; table ranges generally expand as records are added.
  • Check whether another filter is already active before showing all data or applying a new view.
  • Remember that manually hidden rows and filtered rows are different; clearing a filter does not necessarily unhide rows hidden manually.

AutoFilter compared with Advanced Filter

AutoFilter is usually the straightforward choice for an interactive, reversible view of a list. It is suited to routine criteria, multiple dropdown selections, and macros that repeatedly show a useful slice of a table. It leaves the source data in place and lets the user change or clear the filter.

Advanced Filter is a better fit when criteria need a separate criteria range, when the filtering logic is more involved, or when matching records should be copied to another location. It can also extract unique records. That extra flexibility comes with more setup: the criteria range needs correctly arranged headings and conditions, and the macro must identify the source, criteria, and output ranges correctly. Neither feature is a substitute for deleting records; deletion should be a separate, deliberate operation.

Common problems and how to diagnose them

If nothing appears after a macro runs, first verify the header row, the field number relative to the range, and the exact values in the target column. A trailing space in a cell, a misspelled criterion, or text that looks like a number can prevent a match. Then inspect whether another column already has an active filter that excludes the expected records.

If new rows are not included, the code may be filtering a fixed range that ends above them. If the filter arrows appear over the wrong headings, the range likely starts or ends in the wrong place, or the Field number was counted from the worksheet rather than from the range’s left edge. If ShowAllData causes an error, check that the worksheet is filtered before calling it and that the intended list is the one being managed.

Another common misconception is that AutoFilter changes the data or makes formulas ignore hidden rows. It does neither by itself. Many ordinary calculations still include filtered-out rows; functions such as SUBTOTAL are designed for calculations that can respond to filtered lists, depending on the function number used. A macro that copies or deletes data also needs explicit logic to target visible cells safely.

Frequently Asked Questions

Does VBA AutoFilter delete rows?
No. It hides rows that do not satisfy the criteria. The records remain in the worksheet and can be displayed by clearing the filter.

What does Field mean in AutoFilter?
Field is the column’s position within the range passed to AutoFilter, counted from the range’s leftmost column. In a range starting at C1, Field 1 refers to column C, not column A.

Can AutoFilter apply criteria to several columns?
Yes. Apply criteria to different Field numbers in the same list. Filters on separate fields combine, so a visible row must meet all of those field conditions.

How do I show all filtered rows again?
Clear the active filter from Excel’s filter controls, or use ShowAllData after confirming that the worksheet is in FilterMode. Clearing the criteria does not delete records.

Why does my AutoFilter macro return no matches?
Check the range, header row, Field number, and exact data values. Also look for other active filters, extra spaces, dates stored as text, or numbers stored as text.

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.