Excel VBA AutoFilter: How to Apply Filters with Code

VBA & macros · Updated November 21, 2026

In Excel VBA, apply AutoFilter to a range that includes its header row, then set the field number and criterion—for example, filter the fourth column for “Open.” The key method is Range.AutoFilter; the example below shows a basic macro for a worksheet named Orders.

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

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

    ' Filter the fourth column, assumed to be Status.
    dataRange.AutoFilter Field:=4, Criteria1:="Open"
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. This article focuses on desktop Excel for Microsoft 365 on Windows; Mac and web differences are covered below.

What AutoFilter does in VBA

AutoFilter adds or uses filter controls on a header row and hides rows that do not match the selected criteria. It does not delete the excluded records: clearing the filter makes them visible again. In VBA, you can apply a filter to a worksheet range or to an Excel table, which is a ListObject.

A range used for filtering should be a single rectangular dataset with one header row and consistent columns. If the data starts in A1, CurrentRegion commonly selects the contiguous block around that cell. It stops at blank rows or columns, so it may omit records if the dataset contains a gap. For a known, fixed layout, an explicit range such as A1:F500 can be more predictable; for a growing dataset, a table usually adjusts more reliably.

AutoFilter changes which rows are displayed, not the order or contents of the source data. If another macro needs to process only visible records, it must account for hidden rows rather than assuming that the filtered range contains only matching values.

Understanding Field and the header row

The Field argument is a one-based position within the range being filtered, not the worksheet’s column number. If the filtered range begins in column C, Field 1 means C, Field 2 means D, and Field 4 means F. This is a frequent source of mistakes when code uses a range that starts somewhere other than column A.

The first row of the range is treated as the header row. Therefore, if A1:D100 contains headings in row 1 and records below, Field 4 refers to column D. If the range begins at A2, the first data row may be mistaken for the header, and the field numbering will no longer mean what the author intended. Include the headings in the range even when the macro’s goal is to filter only the records.

Headers should be distinct and descriptive. Duplicate or blank headings can make a filter confusing to use, even when the field number in VBA is technically valid. If a worksheet has a title above the data, start the filter range at the actual header row rather than at the title.

Filter text, numbers, and multiple values

For a single text match, use Criteria1. For numeric comparisons, pass a comparison string such as >=100. The following example assumes A1:D200 is the dataset and column C is Amount; it keeps rows where Amount is at least 100 and less than 500.

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

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

    ' Field 3 is column C within A:D.
    dataRange.AutoFilter Field:=3, Criteria1:=">=100", _
        Operator:=xlAnd, Criteria2:="<500"
End Sub

With text, criteria such as “Open” generally match regardless of letter case. Wildcards can help with partial matches: an asterisk represents any sequence of characters and a question mark represents one character. For example, a criterion of *North* can match text containing North. If a literal wildcard character is part of the value, escaping and matching behavior can require extra care.

To show more than one specific text value in the same field, use xlFilterValues and an array. In this example, A1:D200 is the range and Field 2 is Region:

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

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

    ' Keep records whose Region is East or West.
    dataRange.AutoFilter Field:=2, _
        Criteria1:=Array("East", "West"), Operator:=xlFilterValues
End Sub

For date filtering, confirm that the cells contain real Excel dates rather than text that only looks like a date. Date criteria can be affected by regional date conventions and by whether the data includes time values. When a boundary matters, filtering from the start of one date up to—but not including—the next date avoids accidentally excluding times later on the final day.

Apply a filter to an Excel table

Tables are often a better target than a manually sized range. They expand when records are added, include their own header row, and have built-in filter controls. In VBA, refer to the table by its ListObject name rather than relying on its position on the sheet. This macro assumes a table named SalesTable on the Orders sheet and that its fourth table column is Status.

Sub FilterSalesTable()
    Dim ws As Worksheet
    Dim salesTable As ListObject

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set salesTable = ws.ListObjects("SalesTable")

    ' Filter the table's fourth column for Open records.
    salesTable.Range.AutoFilter Field:=4, Criteria1:="Open"
End Sub

Table field numbers are relative to the table’s first column, just as range field numbers are relative to the filtered range’s first column. A table can also be referred to by its column names for tasks that need to locate a field dynamically, but the AutoFilter Field argument itself still takes a numeric position. Check the table name under Table Design > Table Name if a macro reports that it cannot find the ListObject.

Clear criteria or remove filter arrows

There is a difference between clearing the active criteria and turning off the filter controls. On a normal worksheet range, ShowAllData displays all rows again while leaving the dropdown arrows available. Check FilterMode first because calling ShowAllData when no filter is active can cause an error.

Sub ShowAllRows()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")

    ' Clear criteria only when the sheet is currently filtered.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

To remove worksheet AutoFilter arrows for a plain range, set the worksheet’s AutoFilterMode property to False. Do this only when the macro owns that range’s filter controls: a worksheet may have other filters or a table whose controls should remain. For a table, use its filter interface rather than casually switching off worksheet-level settings. If the goal is just to reset what the user sees, clearing criteria is usually less disruptive than removing the arrows.

Common problems and how to diagnose them

  • The wrong column is filtered: Recount Field from the left edge of the exact range passed to AutoFilter. It starts at 1, not at the worksheet’s column number.
  • Some records are missing from the filter range: Check for blank rows or columns that split a CurrentRegion, or use a table or a deliberately sized range.
  • The macro fails on ShowAllData: Test FilterMode before calling it. A filter dropdown can exist even when no criteria are currently hiding rows.
  • A number or date criterion matches nothing: Check the underlying cell values and data types. Numbers stored as text and dates stored as text may not behave like numeric values or Excel dates.
  • Another filter seems to disappear: Determine whether the range is a table or shares worksheet-level filter settings with other data. Avoid clearing or disabling filters globally if the macro should affect just one dataset.

Also check that the sheet name, range address, and table name match the workbook exactly. A macro that targets the active sheet can appear to work inconsistently because the result depends on which sheet was selected when it ran. Using an explicit worksheet reference, as in the examples, makes the target clear.

Windows, Mac, and Excel for the web

The examples are intended for Excel for Microsoft 365 on Windows, where the Range.AutoFilter method can be used directly. Excel for Mac supports VBA, but Microsoft documents that the Range.AutoFilter method is not supported on Mac; similar AutoFilter methods on Selection and ListObject are supported. If adapting a plain-range macro on Mac, use an appropriate supported object method and verify the behavior in the installed Excel version. A table-based approach may be preferable, but should still be checked in the target environment.

Excel for the web does not run VBA macros. A workbook can be opened there, but to execute VBA, open it in the desktop Excel application with macros enabled. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the editor is available from Tools > Macro > Visual Basic Editor, though keyboard shortcuts can vary by keyboard and Excel settings. Use the desktop interface’s macro security prompts carefully, and enable macros only in workbooks you trust.

When to use VBA instead of a manual filter

Use VBA when the same filtering operation needs to run repeatedly, when a button or larger procedure should update a report, or when several criteria must be applied consistently. A manual filter is simpler for one-off exploration and lets a user inspect choices without editing code. AutoFilter is also not the same as the worksheet FILTER function: AutoFilter hides nonmatching rows in place, whereas a formula-based FILTER returns matching values into another range and is available in Microsoft 365 and Excel 2021 or later.

Choose the simplest method that meets the need. A table with built-in dropdowns is often enough for an interactive list. A macro is useful when the action is repeatable, but it should state its target range and assumptions clearly, preserve other users’ filter settings where possible, and avoid deleting data as a substitute for filtering.

Frequently Asked Questions

Does AutoFilter delete rows that do not match?
No. It hides nonmatching rows from view. Clear the filter to display those records again; filtering is not a deletion operation.

Does Field mean the worksheet column number?
No. Field is the one-based column position within the range supplied to AutoFilter. In a range starting at C, Field 1 refers to worksheet column C.

How do I filter for several text values?
Pass an array to Criteria1 and set Operator to xlFilterValues. The example above filters one field for East or West.

How can I show all rows but keep the dropdown arrows?
On a worksheet with an active filter, call ShowAllData after checking FilterMode. This clears the criteria but leaves the filter controls in place.

Can I run an AutoFilter macro in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in desktop Excel to run the macro; Mac users should also account for the documented difference in Range.AutoFilter support.

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.