In Excel VBA, the AutoFilter header is the first row of the range you pass to AutoFilter; include that row in the range, and Excel treats the remaining rows as records. For example, if row 1 contains headers in A:D, filter the range A1:D100 with Field:=2 to filter its second column, B.
How AutoFilter treats the header row
AutoFilter does not take a separate argument for the header row. Instead, it treats the first row of the specified range as the row of column labels. This means the range’s starting row matters more than whether the worksheet’s first row is a header. A dataset beginning on row 5 can use A5:D100; Excel treats row 5 as its header.
Excel displays filter arrows in the header cells and applies criteria to the records below them. Formatting a row in bold, freezing it, or giving it a particular worksheet row number does not tell VBA that it is a header. The range you specify establishes that boundary.
For reliable results, use one header row with a label for each column, followed by records in consistent columns. Avoid merged header cells and blank rows or columns inside the data. A blank header may still receive a filter arrow, but it is harder for a person to identify and can make later maintenance confusing.
Choose a range that includes the headers
The most common header problem is selecting only the data rows. If you pass A2:D100 when row 1 contains the labels, Excel treats row 2 as the header and the actual labels are left outside the filter. Start the range on the real header row instead.
You can use a fixed address when the dataset size is known, or use CurrentRegion when the data forms a contiguous block. For example, ws.Range("A1").CurrentRegion expands from A1 to the surrounding block, stopping at fully blank rows or columns. It is convenient, but a blank separator inside a dataset can make the detected region smaller than expected. Check that the first cell is actually in the dataset and that the region includes all intended columns and records.
For a deliberately bounded range, specify it directly, such as A5:D250. That is often clearer when a sheet contains notes, totals, or other blocks nearby. If the data grows over time, an Excel Table is usually easier to maintain than repeatedly editing a fixed ending row.
Apply a filter with VBA
Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. The following example assumes a worksheet named Data, headers in A1:D1, and records below them; it filters the second range column, Region, to show West.
Sub FilterByRegion()
Dim ws As Worksheet
Dim filterRange As Range
' Assumes row 1 contains headers and the data is contiguous from A1.
Set ws = ThisWorkbook.Worksheets("Data")
Set filterRange = ws.Range("A1").CurrentRegion
' Field 2 means the second column within filterRange.
filterRange.AutoFilter Field:=2, Criteria1:="West"
End Sub
The range’s first row must contain the intended headers. If the block begins at A5 instead, use A5 as the starting cell or set an explicit range such as A5:D250. The macro applies a filter; it does not create or repair missing header labels. If an existing filter is already active, this call applies the requested criterion to field 2 and may leave other field criteria in place.
Understand the Field number
Field is a one-based position within the range passed to AutoFilter, not a worksheet column number and not a header name. In a range that starts in column A, Field 2 refers to B. In a range that starts in column C, Field 1 refers to C, Field 2 to D, and Field 3 to E.
This distinction explains a frequent VBA error: a programmer sees that the desired data is in worksheet column E and writes Field:=5, even though the filter range starts in C. The correct field number is 3 because E is the third column of C:G. If the range starts at A, then E is Field 5.
The field position is also not found by writing the visible header text into the Field argument. To filter a column called Region, first determine where Region sits within the chosen range, then supply that position. If columns are rearranged often, calculate the position from the header row rather than relying on a fixed number, or use a table with clearly managed column names.
Use an Excel Table for changing data
An Excel Table (a ListObject in VBA) has a defined header row and expands as records are added. To create one in Microsoft 365 for Windows, select the data and choose Insert > Table, then confirm that My table has headers is selected. Assigning a meaningful table name makes VBA code easier to read than relying on a worksheet address.
For a table named Sales, its range includes the table headers and records. The field numbering for filtering its range is still relative to the first table column. For example, when Region is the second table column, the second field is the one to filter even if the table begins in worksheet column F. Tables also provide filter arrows by default and generally adjust their range as rows are added.
Do not confuse the Table’s header labels with a requirement that the labels be unique for ordinary range filtering. For VBA, the Field number identifies a position. However, clear, distinct labels make the workbook easier to use, and Excel Tables manage column names more predictably than a loosely defined range.
Clear criteria without removing the header
Clearing a filter criterion is different from turning off the filter arrows. If a worksheet is filtered, ShowAllData shows all records again while leaving the AutoFilter controls available. A guarded version is:
Sub ShowAllFilteredRows()
Dim ws As Worksheet
' Save a workbook copy before running macros; Ctrl+Z cannot undo VBA.
Set ws = ThisWorkbook.Worksheets("Data")
' FilterMode is True when rows are currently filtered.
If ws.FilterMode Then ws.ShowAllData
End Sub
AutoFilterMode indicates whether filter arrows are displayed; FilterMode indicates whether the sheet is currently filtered. They are not interchangeable. Showing all rows does not remove the header or necessarily remove the arrows. Avoid toggling AutoFilter blindly: calling AutoFilter with no criteria can turn arrows on or off, so repeated runs may not have the intended effect.
Common header and range problems
- Wrong first row: The range starts one row too low or too high, so Excel treats a record as the header or excludes the labels. Set the range’s first row to the actual header row.
- Wrong Field value: The number is counted from the worksheet’s left edge instead of the filter range’s left edge. Count columns inside the range, starting at 1.
- Unexpected CurrentRegion: An empty row or column splits the data block. Inspect the resulting range or specify the address explicitly.
- Header-like data appears in the list: The real header may have been omitted from the range, causing its label to be treated as a record. Include the label row.
- Some records do not respond: They may fall outside a fixed range, or may be separated by blank rows or columns. Extend the address or use a Table.
- Criteria do not match: The Field may be correct, but the criterion differs from the cell values. Check spaces, spelling, data types, and whether the values are dates or numbers.
When debugging, inspect the range before applying the filter. In the VBA editor, you can add a breakpoint and examine filterRange.Address. Confirm its top row, leftmost column, and last row. Then verify that the desired header is in the expected relative position. This separates a header or range error from a criteria error.
Excel for Mac and Excel for the web
The examples above target Excel for Microsoft 365 on Windows. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use the desktop Excel app to run this code. Excel for Mac supports VBA, but Microsoft documents platform differences for the Range.AutoFilter method. If a range-based AutoFilter call fails on a Mac, check the current Microsoft documentation for that Excel version and consider using a Table-based workflow, whose filtering support differs from the plain Range method.
There is no keyboard shortcut that changes which row VBA considers the header: that is determined by the range passed in code. Windows shortcuts for using worksheet filters may differ from Mac shortcuts and keyboard settings, but they do not alter the VBA range boundary. For automation, explicitly setting and checking the range is more dependable than relying on a user shortcut.
Frequently Asked Questions
Does VBA AutoFilter use the first row as a header?
Yes. It treats the first row of the specified range as the header row. Include the actual labels in that range.
How do I filter a header on row 3?
Start the range on row 3, for example A3:D100, if row 3 contains the labels. The worksheet row number does not matter; the first row of the range does.
Does Field:=2 mean worksheet column B?
Only if the filter range starts in column A. Field numbers count from the left edge of the range, so Field 2 is the second column within that range.
Can I filter by the header name instead of Field?
Not directly with the Field argument: it takes a numeric position. Find the header’s position within the range first, or use a table and determine the position of its column.
How do I remove a filter but keep the dropdown arrows?
Use ShowAllData when the worksheet is filtered. That displays all rows without requiring you to remove the AutoFilter controls.
Why is my header showing as a filter result?
The range likely starts below the header, or the intended header row was not included. Set the range’s first row to the row containing the column labels.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Check Whether a Worksheet ExistsTo check whether a worksheet exists in Excel VBA, loop through the target workbook’s Worksheets collection and compare…
- Excel VBA Database Connections: Setup, Code, and SafetyExcel VBA can connect to a database on Windows by using a data provider, opening a connection, and…
- Excel VBA TextBox: Add, Configure, and Use It in FormsAn Excel VBA TextBox is a text-entry control that lets a workbook user type information, such as a…
- Excel VBA Hyperlinks: Add, Edit, and Troubleshoot ThemIn Excel VBA, add a hyperlink with the Hyperlinks.Add method, specifying the cell or shape to hold it…
- VBA Find in Array: Choose the Right Search MethodTo find a value in a VBA array, loop from LBound to UBound and compare each element with…
- Excel VBA InputBox Syntax: Arguments and ExamplesIn Excel VBA, the basic syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.