VBA AutoFilter: What True Means and How to Use It

VBA & macros · Updated November 18, 2026

Yes, VBA AutoFilter uses True in some related settings, but True is not a universal command to filter rows. In Excel VBA, the key distinction is that AutoFilterMode reports whether filter arrows are displayed, while FilterMode reports whether a filter is currently hiding rows.

What “True” means in VBA AutoFilter

In VBA, True is a Boolean value: it represents an enabled, active, or yes condition, depending on the property or argument it belongs to. That makes the phrase “AutoFilter True” ambiguous. It might refer to checking whether a worksheet has filter arrows, checking whether its records are filtered, or setting an option when applying a filter.

For example, this expression asks whether the active sheet displays AutoFilter dropdown arrows: ActiveSheet.AutoFilterMode. It returns either True or False. By contrast, this statement sets the worksheet’s AutoFilter arrows to appear: ActiveSheet.AutoFilterMode = True. It does not, by itself, choose a field or hide rows based on a criterion.

To filter actual data, use the AutoFilter method with a field and a criterion. For example, a range can be filtered to show only records whose second field contains “West.” The word True is not needed for that basic operation.

AutoFilterMode versus FilterMode

These two worksheet properties sound similar, but they answer different questions and are not interchangeable:

  • Worksheet.AutoFilterMode is True when AutoFilter dropdown arrows are displayed on the worksheet. It is False when those arrows are not displayed. You can set this property to True or False.
  • Worksheet.FilterMode is True when the worksheet is currently in a filtered state—that is, a filter is hiding one or more rows. It is False when no active filter is hiding rows. This property is read-only.

It is entirely possible for AutoFilterMode to be True while FilterMode is False. That is the normal state when arrows are available but all records are showing. A worksheet can also have filter arrows and then have a criterion applied, making both properties True. Do not treat AutoFilterMode = True as proof that rows are filtered.

How the AutoFilter method works

The Range.AutoFilter method applies or changes an AutoFilter on a range. Its commonly used arguments include Field, Criteria1, Operator, Criteria2, and VisibleDropDown. For a straightforward filter, specify the field number and criterion, such as Field:=2, Criteria1:="West".

The field number is counted from the left edge of the range you filter, not necessarily from worksheet column A. If the range is B1:D100, field 1 refers to column B, field 2 to column C, and field 3 to column D. The first row of the range should contain the column headings; Excel uses those headings for the filter dropdowns.

VisibleDropDown is the argument most likely to cause confusion about “True.” Setting VisibleDropDown:=True shows the dropdown arrow for the specified field; setting it to False hides that arrow. It controls the arrow’s visibility, not whether matching data rows are included. The criterion controls which records are shown.

Example: filter a range for West

Suppose a worksheet named Orders has headings in row 1, with Region in column B, and the contiguous table runs from A1 through C100. The following macro filters the second field—Region—to display West. It assumes the stated range is the complete data range, including headings.

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

    ' Identify the worksheet and the table, including its header row.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1:C100")

    ' Show only records where the second field equals West.
    dataRange.AutoFilter Field:=2, Criteria1:="West"
End Sub

Run VBA macros only after saving a copy of the workbook; macro changes cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, paste the code, and run the procedure. On Mac, the Visual Basic Editor shortcut is Option+F11 (on some keyboards, you may also need the Fn key). Excel for the web can open workbooks containing VBA, but it cannot create, edit, or run VBA macros.

The example filters the data; it does not set AutoFilterMode to a Boolean value. If the range has no filter arrows, applying the method with criteria normally creates the filter and displays its arrows. Make sure the range includes the intended headers and all records, or the filter may cover only part of the table.

Filtering with other criteria

AutoFilter can use text, numbers, dates, and comparison criteria. For example, Criteria1:=">=100" can display values of 100 or more in the selected field. Text criteria can use wildcards: Criteria1:="North*" matches text beginning with “North.” The criterion’s meaning depends on the type of values in the column and how Excel recognizes them.

For two conditions on one field, use an operator such as xlAnd or xlOr. For example, a numeric field can be limited to values at least 100 and at most 500 by supplying two criteria with Operator:=xlAnd. When working with dates, mixed data types, or special filters such as top-ten items, check the method’s argument requirements for the specific filter you need; not every filter is expressed as a simple text criterion.

When a filter applies correctly but shows no records, inspect the actual cell values and criterion. Extra spaces, inconsistent spelling, numbers stored as text, and date values that include unexpected times can all make a criterion behave differently than intended. Also confirm that the field number is relative to the selected range.

Checking and clearing a filter

Use FilterMode when you need to know whether rows are currently hidden by an applied filter. To remove the active criteria but keep the dropdown arrows, call ShowAllData only when FilterMode is True. To remove the arrows themselves, set AutoFilterMode to False.

Sub ClearWorksheetFilter()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Orders")

    ' Clear criteria only if a filter is hiding rows.
    If ws.FilterMode Then
        ws.ShowAllData
    End If

    ' Remove the AutoFilter arrows from the worksheet.
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

The distinction matters in automation. If you only want every record visible again but expect users to keep filtering, omit the final block that sets AutoFilterMode to False. If you remove the arrows, you remove the worksheet’s AutoFilter interface; that is different from clearing one criterion.

Common problems and misconceptions

  • Setting a property but expecting rows to filter: AutoFilterMode = True concerns the arrows, not a matching rule. Apply Range.AutoFilter with a field and criterion to filter records.
  • Calling ShowAllData when nothing is filtered: This can raise an error if there is no active filter. Check FilterMode first, as in the example.
  • Using the wrong field index: Field numbers start at the left edge of the filtered range. They are not always worksheet column numbers.
  • Filtering a range that stops too soon: If the range excludes rows or columns, those records may not participate in the filter. Confirm the range boundaries and header row.
  • Confusing hidden rows with filtered rows: Rows can be hidden manually or by other code. FilterMode describes AutoFilter state, not every possible reason a row is invisible.

Excel for Mac and workbook considerations

The ribbon’s ordinary filter controls are available in current desktop Excel for both Windows and Mac, though keyboard shortcuts and some interface details differ. VBA compatibility can be different: Microsoft’s documentation notes that the Range.AutoFilter method is not supported on Excel for Mac. If a macro using that method must run on Mac, verify the target Excel version and test the intended workflow there rather than assuming Windows behavior carries over. Excel for the web does not run VBA.

For a reusable dataset, an Excel table can be a convenient alternative to a fixed range: tables include filter controls and expand as rows are added. VBA can also work with a table’s ListObject object model, but that is a different approach from simply using a worksheet range. Choose a table when the data is a structured list that grows; use a range when the boundaries and layout are deliberate and stable.

Frequently Asked Questions

Does AutoFilter True mean the rows are filtered?
No. AutoFilterMode = True means filter dropdown arrows are displayed. Check FilterMode to determine whether AutoFilter is currently hiding rows.

How do I turn on AutoFilter in VBA?
To apply a filter to data, call AutoFilter on the range and provide a field and criterion. Setting AutoFilterMode = True addresses arrow visibility; it does not specify which records should match.

How do I remove a filter but keep the arrows?
If FilterMode is True, use ShowAllData to show all records while retaining the filter controls. Do not set AutoFilterMode = False if you want to keep those arrows.

Can AutoFilterMode be False while FilterMode is True?
These properties describe separate aspects of worksheet filter state, and code should not assume one is a substitute for the other. Check the relevant property for the operation you need, and use Excel’s filter controls to confirm the worksheet’s current state.

Can I run VBA AutoFilter in Excel for the web?
No. Excel for the web does not run VBA macros. Use the desktop Excel app to run VBA, or use the web app’s built-in filtering controls for manual filtering.

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.