VBA AutoFilter Header: How to Set the Header Row in Excel

VBA & macros · Updated November 18, 2026

In VBA, the first row of the range you pass to AutoFilter is treated as the header row; there is no Header:= argument for AutoFilter. Include the column labels at the top of the range, then set Field to the column number relative to that range.

How VBA AutoFilter identifies the header

AutoFilter works on a rectangular range of cells. Excel treats the range’s top row as column headings and adds filter controls to those headings. The rows beneath it are treated as records that can be included or hidden according to your criteria. The header does not have to be row 1 of the worksheet: it only has to be the first row of the range supplied to AutoFilter.

For example, if the labels are in A1:D1 and records run through row 100, the range is A1:D100. If the labels are instead in row 5 and records run through row 100, use A5:D100. In the second example, row 5 is the header because it is the range’s first row. Starting the range at A6 would cause Excel to treat the first record as the heading and produce misleading filter labels.

This is a frequent source of confusion because VBA sorting uses a different convention. A call to Range.Sort can include an argument such as Header:=xlYes or Header:=xlNo. That argument belongs to sorting, not AutoFilter. For AutoFilter, arrange the range so its first row is the intended header.

A basic VBA example

Suppose a worksheet named Sales has headings in A1:D1, with Region in column B, and the data occupies rows 2 through 100. The following macro filters the Region field to show East:

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

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("A1:D100") 'Row 1 contains the headings

    dataRange.AutoFilter Field:=2, Criteria1:="East"
End Sub

Make a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z. On Windows, you can run the macro from the VBA editor or from Excel’s macro controls. Excel for the web cannot run VBA macros.

The example assumes the sheet and range exist and that the field you want to filter is inside the range. It applies a filter; it does not delete or move records. Excel hides rows that do not match, while leaving their cell contents intact. If you already have filters on the worksheet, consider how the new call will interact with them before running it, particularly if the existing filter covers a different range.

What the Field number means

Field is a one-based position within the AutoFilter range, not a worksheet column number. In A1:D100, Field 1 means column A, Field 2 means B, Field 3 means C, and Field 4 means D. If your range begins at C5, Field 1 means worksheet column C. This relative numbering lets the same code work when a table or data block is moved, provided the range still begins at its first column.

Check the boundaries before choosing a field. If you accidentally use A1:C100 but intend to filter column D, Field 4 is outside the range. If you use B1:E100, Field 2 refers to worksheet column C, not column B. A simple way to avoid mistakes is to write down the first column of the range and count across to the desired heading.

AutoFilter applies criteria to the field’s data values, using the heading to label the filter dropdown. It does not require the heading’s text to match the name in your VBA code. For example, filtering Field 2 with the criterion East works because the second included column contains the region values; “Region” is useful as a label but is not part of the criteria expression.

Prepare reliable headers and data

Good headings make both the dropdowns and later maintenance clearer. Use one row of meaningful labels, preferably with a distinct, nonblank name for every column. Avoid merged cells in the header and avoid blank rows or columns inside the data block. A gap can make automatic range selection stop too early, while merged headings can make the filter layout difficult to interpret.

Before filtering, confirm that:

  • The first row of the supplied range contains the intended labels.
  • Each column has one heading cell and each record occupies one row.
  • The range includes every column that belongs to the records, not just the field being filtered.
  • There are no completely blank rows splitting the records into separate blocks.
  • The criterion matches the actual values, including spaces and text-versus-number differences.

Including the full record width matters. If you filter only column B while related names, dates, and amounts sit in columns A, C, and D, Excel may hide cells in B without presenting a coherent filter over the whole record set. A single rectangular range keeps each row together as a record.

Choosing a fixed range, CurrentRegion, or a table

A fixed range such as A1:D100 is straightforward when the data size is stable. Its weakness is that new rows below row 100 are outside the filter. If the number of records changes, the code must be updated or the range must be calculated dynamically.

CurrentRegion can be convenient for a continuous block surrounded by blank rows and columns. For example, a range anchored at A1 can use its CurrentRegion to include the connected data area. It depends on the layout, however: a blank row or column can cut the region short, and nearby notes or totals can be included if they touch the block.

An Excel Table is often the most maintainable choice for data that grows. Tables have named columns and expand as records are added, and they include filter controls by default. In VBA, you can refer to a table through the worksheet’s ListObjects collection and work with its range or columns. If you use a table, avoid layering a separate worksheet AutoFilter over the same data without understanding which filter controls you are changing. Table filters are associated with the table and are generally easier to keep aligned as its size changes.

Choose based on how the source is maintained: a fixed range suits a stable report, CurrentRegion suits a clean contiguous block, and a Table suits a dataset that regularly gains rows or needs structured column references.

Criteria and common filter patterns

The Criteria1 argument supplies the condition for the selected field. For an exact text match, a value such as "East" is typical. For a numeric field, the criterion can be a number or a comparison string such as ">=100". Text criteria generally need to reflect the cell values; leading or trailing spaces can make a visually similar value fail to match.

You can filter more than one field by applying AutoFilter to each field in the same range. For instance, one criterion can select East in the Region field, while another selects values at or above a threshold in a Sales field. These conditions are combined across fields, so a row must satisfy both to remain visible. Within one field, the available operator and criteria arguments determine whether conditions are combined as alternatives or as a more specialized filter.

Dates deserve extra care. Excel stores dates as serial values, and date filters may depend on the workbook’s date system and how values were entered. If a date criterion behaves unexpectedly, check that the cells contain real Excel dates rather than text that merely looks like a date. Also check whether the cells include times; a value with a time component may not match a criterion intended for midnight on that date.

Common problems and how to fix them

If the dropdowns appear on the wrong row, inspect the first row of the range passed to AutoFilter. The method does not search the worksheet for the row that looks most like a heading. It simply uses the range’s first row as the header. Adjust the range’s starting row, then check that the intended data rows are included beneath it.

If the wrong column is filtered, recalculate Field relative to the range’s left edge. If some records do not respond, check whether they fall outside the range, whether a blank separator split the data, or whether the values contain hidden spaces or inconsistent data types. A filter that returns no visible records can also be correct if no cell matches the criterion exactly.

If a filter seems to leave rows hidden after the macro runs, the worksheet may have an active criterion from an earlier action. To show all records while keeping filter dropdowns, Excel’s Data tab includes a Clear command in the Sort & Filter group. To remove the dropdown controls themselves, use Data > Filter to toggle filtering off. In VBA, the worksheet’s filter state must be checked before calling a method that shows all data; calling ShowAllData when no filter is active can raise an error.

Also distinguish filtering from deleting. AutoFilter hides nonmatching rows; it does not remove them. Do not delete visible or hidden rows unless that is specifically intended, and make a backup first if a later macro step performs deletions.

Windows, Mac, and Excel for the web

The code example is intended for Excel for Microsoft 365 on Windows. Microsoft’s VBA reference marks the Range.AutoFilter method as unsupported on Excel for Mac, so do not assume that a Windows macro using this method will run unchanged on Mac. Mac users can apply filters through Excel’s Data tab, or use a Mac-specific VBA approach only after checking that the required object-model method is supported in their Excel version.

Excel for the web can display and use worksheet filters, but it does not execute VBA macros. To run this macro, open the workbook in a desktop version of Excel that supports the VBA code. Menu and keyboard details can differ between Windows and Mac, but the key concept remains the same: the header is the top row of the range being filtered.

Frequently Asked Questions

Does VBA AutoFilter have a Header argument?
No. AutoFilter treats the first row of its specified range as the header. Header:=xlYes is associated with sorting, not the AutoFilter method.

Can the header be on row 5 instead of row 1?
Yes. Start the AutoFilter range on row 5, such as A5:D100. The first row of that range becomes the header, regardless of its worksheet row number.

Is Field 2 the second worksheet column?
It is the second column in the selected range. If the range starts in column C, Field 2 means worksheet column D.

Does AutoFilter delete rows that do not match?
No. It hides nonmatching rows. The underlying records remain in the worksheet and can be shown again by clearing the filter.

Why are filter arrows on my first data row?
The range probably starts one row too low, so Excel is treating that first record as the heading. Change the range to begin on the actual header row and include the records below it.

Can I run this VBA code in Excel for the web or Mac?
Excel for the web does not run VBA. The documented Range.AutoFilter method is unsupported on Mac, so use the desktop Windows version for this example or check a Mac-compatible alternative before relying on VBA.

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.