Excel VBA AutoFilter: Filter Data with Macros

VBA & macros · Updated November 21, 2026

To filter worksheet data with Excel VBA, call the AutoFilter method on the range that includes the column headers, then specify the column number with Field and the value or condition with Criteria1. For example, dataRange.AutoFilter Field:=3, Criteria1:="Open" shows rows whose third column is Open; the field number is counted from the left edge of that range, not from the worksheet’s column A.

What AutoFilter does in VBA

Excel’s AutoFilter hides rows that do not match selected criteria, leaving matching records visible. It does not delete or rearrange the underlying records. This makes it useful for macros that need to show a subset of a list, such as open orders, sales for one region, or values above a threshold.

In an AutoFilter range, the first row is treated as the header row. A range like A1:D500 therefore has four filter fields: column A is field 1, B is field 2, C is field 3, and D is field 4. If the filtered range starts in column C instead, column C is field 1. Include only one header row and make sure each header cell contains a label.

AutoFilter is a good fit for interactive filtering and straightforward criteria. It is different from deleting rows, sorting records, or copying matching records to another place. If a later macro processes visible rows, it should account for the fact that filtered-out rows remain in the worksheet.

A basic macro for filtering a range

This example assumes a worksheet named Orders, a contiguous data block beginning at A1, and Status in the third column of that block. Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

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

    ' Use the contiguous list that starts at A1.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Field 3 is the third column within dataRange.
    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

To use the macro, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the procedure into the module. Run it from the editor with the cursor inside the procedure and F5, or run it from Excel using Developer > Macros. If the Developer tab is not visible, enable it in File > Options > Customize Ribbon. Save a macro-enabled workbook as an .xlsm file if you need to keep its VBA code.

On Mac, Excel also supports VBA, but the Visual Basic Editor and keyboard shortcuts differ; use Tools > Macro > Visual Basic Editor if that command is available in your Excel build, or use the Developer tab. Excel for the web can open a workbook containing macros, but it cannot create, edit, or run VBA macros.

Choosing a range that fits the data

The example uses CurrentRegion, which returns a block bordered by blank rows and blank columns. It is convenient when a list is continuous, but a blank row inside a list can make Excel treat the records below it as a separate region. A blank adjacent column can also change the detected size. Check the range boundary if the macro unexpectedly omits records.

For a predictable layout, specify the range explicitly or find its last row. This example assumes headers in A1:D1, records beneath them, and no gaps in column A. Save a copy before running it because macros cannot be reversed with Ctrl+Z.

Sub FilterOrdersByRegion()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Column A determines the final row; A:D are the table fields.
    Set dataRange = ws.Range("A1:D" & lastRow)
    dataRange.AutoFilter Field:=4, Criteria1:="West"
End Sub

Here, field 4 is column D because the selected range starts in A. If column A can be blank in valid records, use a reliably populated key column to find the last row, or use an Excel Table rather than relying on a single column to define the data’s extent. The header should still be included in the filter range.

Filtering with text, numbers, and multiple conditions

For a text match, pass the desired text as a string. For numeric conditions, criteria are usually expressed as strings containing a comparison operator and a number. In the following example, the range begins at A1, and Amount is its second field. Save a copy before running this macro.

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

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Show rows where the second field is at least 500.
    dataRange.AutoFilter Field:=2, Criteria1:=">=500"
End Sub

AutoFilter can apply more than one field criterion. Calling it on field 3 after field 2 adds or changes the filter on field 3 while retaining the other field’s active criterion. For example, a macro could require Amount to be at least 500 and Status to be Open. The field numbers must refer to the same range each time; changing the range’s left edge changes what each field number means.

Within one field, use Operator:=xlAnd or Operator:=xlOr to combine two conditions. For instance, two criteria can identify values within a numeric interval, or match one of two text values. Excel also supports wildcard matching in text criteria: an asterisk represents any number of characters, and a question mark represents one character. Use these carefully, since a wildcard can match more records than an exact value.

Criteria are interpreted according to the field’s data type and Excel’s filter rules. A column containing a mix of numbers stored as text and true numeric values can produce surprising results. For dates, inconsistent text and date values can also make filtering unreliable. Keep each column’s contents consistent, and verify the visible rows after applying a date or wildcard criterion.

Using AutoFilter with an Excel Table

Excel Tables, created with Insert > Table or Ctrl+T on Windows, are often a robust choice for data that grows over time. A table expands as records are added, provides named columns, and includes filter dropdowns in its header row. In VBA, a table is represented by a ListObject; its Range can be filtered without estimating the last used row.

This example assumes a table named OrdersTable with Region as its fourth column. Save a copy of the workbook before running this macro, since its actions cannot be undone with Ctrl+Z.

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

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set orders = ws.ListObjects("OrdersTable")

    ' Field 4 refers to the fourth table column.
    orders.Range.AutoFilter Field:=4, Criteria1:="West"
End Sub

For a table, field numbering is relative to the table’s first column, just as it is for a normal range. The table’s name and column order must match the workbook. A table is especially helpful when new rows are regularly appended, though a macro should still handle an empty table or missing worksheet or table name if those conditions are possible.

Clearing a filter without deleting data

To show all rows again while keeping the filter dropdown arrows, use the worksheet’s ShowAllData method when a filter is active. A safe check is If ws.FilterMode Then ws.ShowAllData. The FilterMode property indicates whether rows are currently hidden by a filter. By contrast, AutoFilterMode indicates whether AutoFilter arrows are displayed; those properties answer different questions.

Do not confuse clearing a filter with removing records. Showing all data changes which rows are visible, not the data itself. Setting AutoFilterMode to False removes worksheet AutoFilter arrows, which is not normally necessary just to reset criteria. Tables manage their own filter controls, so when working with tables, use the table’s filtering interface and check the result on the intended table.

A common workflow is to clear an earlier filter, apply a new condition, and then leave the filtered view for the user. If no filter is active, calling ShowAllData without checking can raise an error. The guard avoids that particular error, but a macro that may encounter multiple independent filtered lists should be designed and tested for the workbook’s layout rather than assuming there is only one filterable range.

Common problems and practical checks

  • The wrong column is filtered: Recount fields from the left edge of the range passed to AutoFilter. Field 1 is the first column in that range, not necessarily worksheet column A.
  • Some records are missing from the range: Check for blank rows or columns when using CurrentRegion, and confirm the last-row calculation uses a column populated in every record.
  • The filter appears to do nothing: Confirm the criterion matches the actual cell contents, including spaces and data type. Also check whether any records meet the criterion and whether the correct worksheet is active in the code.
  • Excel reports an AutoFilter error: Ensure the range includes a header row and that the field number is within its column count. A misspelled worksheet or table name can also stop the macro before filtering.
  • A later step processes hidden rows: Filtering hides nonmatching records but does not remove them. Code that copies or counts only visible rows needs to explicitly account for visibility.

Test a macro on a copy and inspect the filtered results before using it in a larger workflow. Macros may be blocked by security settings or by an organization’s policy, so only enable code from a source you trust. A workbook can retain VBA in .xlsm format, but saving it as a macro-free .xlsx file removes the VBA project.

AutoFilter compared with alternatives

Use AutoFilter when the goal is to display matching rows in place and let someone inspect the original list. For reusable criteria or more complex extraction, Excel’s Advanced Filter can copy matching records to another area and can work with a separate criteria range. It has a different setup and is not the same as calling the range’s AutoFilter method.

For analysis rather than row visibility, a PivotTable may be more suitable for grouping and summarizing records. In Microsoft 365, worksheet functions such as FILTER can return matching rows as a formula-driven result, but that is separate from VBA AutoFilter and requires a version that supports the function. Choose based on whether you need an interactive view, a copied extract, or a calculated summary.

Frequently Asked Questions

How do I filter a column by a cell value in VBA?
Include the header row in the filter range, then call AutoFilter with the field number and criterion. For example, for a range starting at A1 with Status in its third column, use dataRange.AutoFilter Field:=3, Criteria1:="Open".

Does AutoFilter delete rows that do not match?
No. It hides rows that fail the criteria. The records remain in the worksheet and can be shown again by clearing the filter. Use separate deletion code only if you genuinely intend to remove records, and make a backup first.

How do I filter two columns at once in VBA?
Call AutoFilter on the same range for each field, supplying a criterion for each column. For example, apply one call to the Status field and another to the Region field. Each field number is counted from the range’s first column.

Why does Field:=1 filter the wrong column?
Field is relative to the range, not to the worksheet. If the range starts in column C, field 1 is C, field 2 is D, and so on. Include the correct first column in the range or adjust the field number.

Can Excel for the web run a VBA AutoFilter macro?
No. Excel for the web can display and preserve a workbook that contains VBA, but VBA macros cannot be created, edited, or run there. Open the workbook in a desktop version of Excel to run the macro.

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.