In Excel VBA, Field:=1 tells AutoFilter to filter the first column in the filter range—not necessarily worksheet column A. For example, if the filter range is B2:D50, field 1 means column B; if the range is A1:D50, field 1 means column A.
What Field 1 Means
The Field argument is a position within the range that AutoFilter is using. It counts from the range’s left edge, starting at 1. It is not a worksheet column number, a column letter, or the number of the header row. This relative numbering is the key to understanding examples such as Range("A1:C20").AutoFilter Field:=1.
Suppose a worksheet has a report in columns B through D, with headers in row 2. If you filter B2:D50, then Field 1 points to B, Field 2 to C, and Field 3 to D. Changing the filter range to A2:D50 shifts those positions: Field 1 now points to A, and the report’s original first column, B, is Field 2.
AutoFilter treats the first row of its range as the header row. The field number refers to the data column beneath that header, not to the row containing the criteria or the worksheet’s column index. Consequently, including an extra column at the left of the range can change what a Field number selects.
Basic VBA Syntax
The usual pattern is someRange.AutoFilter Field:=number, Criteria1:=value. The range should cover the header row and the records to be filtered. The named argument Field:= makes the code easier to read than relying on the argument’s position. Criteria1 supplies the condition; the field and the criterion do different jobs.
For instance, if a range starts in column A and its first header is Region, Field:=1, Criteria1:="West" displays rows whose Region value matches West. It does not mean “filter worksheet column 1 regardless of the range.” If the same table starts in column C, Field 1 refers to C instead.
In Excel for Microsoft 365 on Windows, Range.AutoFilter is the standard way to apply a filter to a range. The range’s top row should contain meaningful headers, and the data should be arranged as a continuous list rather than separated by completely blank rows or columns.
A Complete Example
Assume a worksheet named Sales contains headers in A1:C1: Region, Product, and Amount. The records occupy rows 2 through 100. The following macro filters the first field, Region, to show West. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.
Sub FilterSalesToWest()
Dim ws As Worksheet
Dim dataRange As Range
' Set the worksheet and include the header row in the filter range.
Set ws = ThisWorkbook.Worksheets("Sales")
Set dataRange = ws.Range("A1:C100")
' Field 1 is the first column of dataRange: column A (Region).
dataRange.AutoFilter Field:=1, Criteria1:="West"
End Sub
To filter Product instead, use Field 2 because Product is the second column within A1:C100. To filter Amount, use Field 3. The same field numbering applies if the range is a named range or a table range: count columns from the left edge of the particular range passed to AutoFilter.
To try it in desktop Excel for Windows, open the VBA editor through Developer > Visual Basic, insert a standard module, and paste the macro there. If Developer is not visible, enable the Developer tab in Excel Options. Run the procedure from the editor or from the Macro dialog. The Windows shortcut for the VBA editor is Alt+F11; on many Mac keyboards it is Option+F11, sometimes together with Fn. Excel for the web does not run VBA macros.
Choosing the Right Range and Field
The correct Field number depends on the exact range used in the AutoFilter call. Before writing the number, identify the range’s first column and count across to the column you mean to filter. A quick way to prevent mistakes is to write down the headers in range order, including any ID or helper columns that precede the visible report.
- For
B2:E100, Field 1 is B, Field 2 is C, Field 3 is D, and Field 4 is E. - For
A1:D100, Field 3 is C, even if the worksheet’s column C is the third worksheet column only by coincidence. - For a table whose first column is OrderID, filtering its third table column means Field 3, even if the table begins in worksheet column F.
Keep the header row in the range. If the range starts one row too low, Excel can treat the first record as the header, which leads to missing data or an unexpected dropdown label. If the range starts too high and includes a title row above the real headers, Excel may use that title as the header instead. Check the first row of the selected range before diagnosing the field number.
When a list grows over time, a fixed address such as A1:C100 can omit new records below row 100. Excel tables are often a better fit for expanding data. A table can be referenced with a ListObject variable, and its range includes its header row. The field argument still counts from the table’s first column; it does not become a worksheet column number.
Using Criteria and Multiple Conditions
The criterion is separate from the field number. Text can be matched by supplying a string, while comparison operators can be used for numeric values. For example, in a range beginning at A1 with Amount in its third column, Field 3 with Criteria1:=">500" selects amounts greater than 500. The comparison symbol belongs inside the criterion string.
For a numeric interval, AutoFilter can combine two conditions on the same field with Operator:=xlAnd. For example, Field 3 with Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500" selects values from 100 through 500, including both endpoints. The field remains 3 for both conditions because both are applied to the same column.
Text criteria can use wildcard characters. An asterisk matches any number of characters and a question mark matches one character, so a criterion such as "North*" can match entries beginning with North. Excel’s standard AutoFilter text matching is not case-sensitive. For criteria involving dates, be mindful that date strings can be interpreted according to regional settings; test the filter against known records, particularly when workbooks move between computers with different locale settings.
For more involved filtering—such as OR conditions across different columns, color filters, or complex date groupings—ordinary AutoFilter arguments can become difficult to maintain. Check the exact conditions needed before adding more arguments, and consider whether a helper column or an Advanced Filter would make the logic clearer.
Common Field 1 Problems
If the macro filters the wrong column, first inspect the range, not just the field number. A call using Range("B1:D20").AutoFilter Field:=1 correctly targets B. A call using Range("A1:D20").AutoFilter Field:=1 targets A. This is the most common source of confusion: the same Field value can point to different worksheet columns when the range changes.
Other frequent causes of unexpected results include:
- Wrong header row: the first row in the range is treated as headers. Move the range start to the actual header row.
- Incomplete range: records outside the specified address are not included. Expand the range or use an Excel table that grows with the data.
- Blank or inconsistent data: blank rows can split a list, while inconsistent values may make a criterion appear not to match. Inspect the source cells, including leading or trailing spaces.
- Existing filters: a worksheet may already have another filter range or active criteria. Confirm which range owns the dropdowns and whether old filters need to be cleared.
- Wrong criterion type: numbers stored as text, dates interpreted under a different locale, or a misspelled text value can produce surprising results.
When debugging, temporarily use a small, known range and a value that visibly exists in its first data column. Confirm the header, count the columns from the range’s left edge, and then expand the code to the full dataset. This isolates a range or field mistake from a data-quality problem.
Clearing Filters and Working with Tables
Applying another criterion to a field replaces that field’s current selection. To clear all active worksheet filters in VBA while retaining the filter controls, check FilterMode before calling ShowAllData. This avoids calling ShowAllData when there is no filtered data to show.
Sub ClearWorksheetFilters()
Dim ws As Worksheet
' Set the worksheet whose active filters should be cleared.
Set ws = ThisWorkbook.Worksheets("Sales")
' Show all records only when the worksheet is currently filtered.
If ws.FilterMode Then ws.ShowAllData
End Sub
This clears active criteria on the worksheet; it is not the same as removing the dropdown arrows. Removing or restoring filter controls is a separate operation, so decide whether the goal is to show every record or to turn AutoFilter off. If several tables or filter ranges are involved, target the relevant table or range rather than assuming a worksheet-level operation affects only one list.
For a structured Excel table, a ListObject reference can make the intent clearer because the table identifies its own data range. For example, after setting a ListObject variable to a table on the worksheet, use its range with Field:=1 to filter its first table column. Tables also expand as rows are added, reducing the risk that a hard-coded range misses new records.
Windows, Mac, and Excel for the Web
The examples using Range.AutoFilter target Excel for Microsoft 365 on Windows. VBA is available in desktop Excel for Mac, but Microsoft documents a platform limitation for the Range.AutoFilter method on Mac. In Mac VBA, the corresponding AutoFilter method on Selection or on a ListObject is supported; a Windows macro that calls Range.AutoFilter may therefore need adjustment. Test platform-specific code on a copy of the workbook.
On Mac, a practical alternative is to work with a table and its filter controls, or adapt the code to use the supported Selection-based method after selecting the intended range. Selection-based code should select only the intended header-and-data range, because it depends on the active worksheet and selection. Excel for the web can display and use worksheet filters, but it does not execute VBA; use desktop Excel to run or edit the macro.
Frequently Asked Questions
Does Field 1 always mean column A?
No. It means the first column in the range passed to AutoFilter. It points to column A only when that range begins in column A.
Do I count the header row when setting Field?
No. The header row is included in the range, but Field counts columns from left to right, beginning with the first column as 1. The header row determines which row supplies the filter headings.
How do I filter the second column in my range?
Use Field:=2 and provide the desired criterion. For example, in A1:C100, Field 2 refers to column B.
Why does Field 1 filter the wrong column?
Usually the range begins in a different column than expected, or it includes an extra column on the left. Check the address used in the AutoFilter call and count from its left edge.
Can VBA AutoFilter run in Excel for the web?
No. Excel for the web supports worksheet filtering, but it does not run VBA macros. Open the workbook in desktop Excel to execute VBA.
Does this Range.AutoFilter code work unchanged on Mac?
Not necessarily. The Range.AutoFilter method has a documented Mac limitation; use the supported Selection or ListObject approach and verify the code in desktop Excel for Mac.
Related
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter Header: How to Set the Header Row in ExcelIn VBA, the first row of the range you pass to AutoFilter is treated as the header row;…
- Excel Macro Button: How to Add, Assign, and Run MacrosAn Excel macro button is a clickable control that runs a VBA macro in a workbook. In Excel…
- Excel VBA Input Box Dropdown List: Options and SetupExcel’s built-in VBA InputBox does not support a dropdown list. To let someone choose from a visible list,…
- Excel VBA: Clear the Clipboard After Copy and PasteTo clear Excel’s copy mode after a VBA copy-and-paste operation, run Application.CutCopyMode = False after the paste. This…
- Excel VBA Copy and Paste Cells: Examples and OptionsTo copy a cell with Excel VBA, use the source range’s Copy method and specify a destination: sourceCell.Copy…
- Excel VBA Integer: Range, Limits, and Type ChoiceIn Excel VBA, Integer is a signed whole-number data type that stores values from -32,768 through 32,767. It…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.