To filter several Excel columns with VBA, call AutoFilter once for each field, using the same data range. Each call adds or replaces that column’s condition, so filters on different columns work together as AND conditions: a row must meet every active column filter to remain visible.
What filtering multiple columns means
Excel’s AutoFilter hides rows that do not match the criteria you choose; it does not delete the records. When you apply a condition to one column and then another, Excel combines the conditions across columns. For example, filtering a Region field for East and a Sales field for values above 1,000 shows records where the region is East and sales exceed 1,000.
There is an important distinction between combining criteria across columns and choosing alternatives within one column. Multiple field filters are normally ANDed together. Within a single field, you can use an OR-style list—for example, East or West—or a pair of comparison conditions. A macro needs to express those two situations differently.
AutoFilter’s Field number is relative to the range being filtered, not the worksheet’s column number. If the range starts at column A, Field 2 means column B. If it starts at column C, Field 2 means column D. Keeping that detail in mind prevents a common source of filters appearing on the wrong column.
Set up the data range and headers
AutoFilter works best on a single, rectangular data list with one header row. Each header should identify one field, and the rows beneath it should contain records. Avoid blank rows or blank columns inside the list because they can split a range or make a detected data area smaller than expected. Merged cells in the header are also best avoided.
In the example below, the list is on a worksheet named Data. Its headers are in A1:D1, with Region in column B and Sales in column D. The range includes both the headers and all rows to be filtered. Change the worksheet name and range to match your workbook. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.
Apply filters to two or more columns
This macro clears any active worksheet filter, adds filter arrows to A1:D100, then filters Region to East and Sales to values greater than 1,000. The filters work together, so only rows matching both conditions remain visible.
Sub FilterSales()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Clear existing worksheet filters before applying this example.
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Add arrows, then filter fields relative to dataRange.
dataRange.AutoFilter
dataRange.AutoFilter Field:=2, Criteria1:="East"
dataRange.AutoFilter Field:=4, Criteria1:=">1000"
End Sub
Here, Field 2 refers to B and Field 4 refers to D because the selected range begins in A. The first condition is an exact text match; the second is a numeric comparison. If your dataset ends on a different row, adjust the range. The header row must be included, or Excel may treat the first record as a header instead.
To use the macro, open the Visual Basic Editor from the Developer tab, insert a standard module, and paste the code there. If the Developer tab is not visible in Excel for Microsoft 365 on Windows, enable it through File > Options > Customize Ribbon. Macro-enabled workbooks must be saved as .xlsm to retain VBA code.
Use OR criteria within one column
To show rows where one field contains either of two exact values, pass an array as Criteria1 and set the operator to xlFilterValues. For example, this filters the Region field to East or West. You can apply it alongside another field’s condition, such as the Sales threshold in the second call.
Sub FilterEastOrWestOver1000()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
dataRange.AutoFilter
dataRange.AutoFilter Field:=2, _
Criteria1:=Array("East", "West"), Operator:=xlFilterValues
dataRange.AutoFilter Field:=4, Criteria1:=">1000"
End Sub
This means (Region is East or West) and Sales exceeds 1,000. The array approach is useful for an explicit list of values. For two comparison rules on the same field, use Operator:=xlAnd or xlOr with Criteria1 and Criteria2. For instance, on a numeric field, criteria of at least 10 and less than 20 define a range. Do not confuse that pair of same-column criteria with filters on two separate fields.
Text, numbers, dates, and blanks
Criteria should match the kind of data in the field. Text criteria such as "East" select a matching label, while comparison criteria such as ">1000" are passed as strings that Excel interprets as comparisons. For a text field, operators such as * and ? can act as wildcards: an asterisk matches a sequence of characters, and a question mark matches one character. Use a tilde before a wildcard when you need to search for the literal character.
Dates can be more delicate because the displayed format depends on regional settings, while Excel stores dates as serial numbers. A date filter may need criteria formatted for the workbook’s locale; test it on a copy and verify the visible results. For a condition such as “after a particular date,” construct the criteria carefully for the date values and Excel version in use rather than assuming a typed date string will behave identically on every computer.
Blank and nonblank cells can also be filtered, but decide whether cells that look empty actually contain a formula returning an empty string. Those cells are not always treated the same as genuinely empty cells. If a filter seems to miss records, inspect the underlying values and data types, not just how the cells look on screen.
Choose a fixed range, dynamic range, or table
A fixed range such as A1:D100 is easy to read and is suitable when the list size is known. Its drawback is that new rows added below row 100 are not included. One alternative is to use the contiguous data area around a known header, for example ws.Range("A1").CurrentRegion. This detects a surrounding block, but a fully blank row or column can break the block, so it is not ideal when gaps are part of the layout.
For a recurring dataset that grows, an Excel Table is often more maintainable. Create one with Insert > Table, make sure the header option is correct, and give it a descriptive name in the Table Design tab. A table expands as rows are added. In VBA, you can refer to its range through a ListObject and apply filters to the table’s fields. Table field numbering is likewise relative to the table, which is useful when the table does not begin in column A.
Choose the method based on how the workbook is maintained: a fixed range is simple for a stable report, CurrentRegion is convenient for a solid contiguous block, and a table is generally clearer for data that grows or is shared with other workbook formulas. Whichever method you use, confirm that every record and header is included in the filter range.
Common problems and how to fix them
- The wrong column is filtered: Recalculate Field from the left edge of the specified range. It is not an absolute worksheet column number.
- Only the last condition seems to work: Check that each call uses the same range and a different Field number. A later call on the same field replaces that field’s existing criteria.
- New records are not included: Expand the fixed range or use a table or a correctly detected dynamic range.
- The macro reports that ShowAllData failed: ShowAllData should only be called when a filter is actively hiding rows. The example checks
FilterModefirst; avoid calling the method unconditionally. - Results differ from what the cell displays: Check for leading or trailing spaces, numbers stored as text, formula-generated empty strings, and inconsistent spelling.
- Filter arrows disappear: Setting
AutoFilterModeto False removes the arrows. The example then reapplies AutoFilter to the data range before setting criteria.
Also check whether another part of the macro changes the range or clears filters afterward. A filtered worksheet can still contain hidden rows from manual hiding or other actions, so visible results may not be explained by AutoFilter alone.
Run macros safely on Windows and Mac
Excel for Microsoft 365 on Windows supports the VBA examples above. Excel for Mac also supports VBA, although the route to the Visual Basic Editor and some keyboard shortcuts differ. The Developer tab can be enabled from Excel’s ribbon settings on either platform; use Developer > Visual Basic to open the editor when that tab is available. Menu labels and system security prompts can vary by platform.
Macros may be blocked by Trust Center or organizational security settings. Only enable macros in files whose source you trust, and do not lower security settings just to run an unfamiliar workbook. Keep an untouched copy before running code, because filter changes can be cleared, but macro actions in general cannot be undone with Ctrl+Z on Windows or the corresponding undo command on Mac.
Frequently Asked Questions
Can VBA AutoFilter apply criteria to multiple columns at once?
Yes. Call AutoFilter separately for each field, using the same range. Each call sets that field’s filter, and criteria on different fields combine so records must satisfy all of them.
Does Excel VBA use AND or OR between columns?
Conditions on separate fields are combined as AND. To express OR choices within one field, use an array with Operator:=xlFilterValues, or use the appropriate pair of criteria on that same field.
What does the Field argument mean in AutoFilter?
It is the field’s position inside the filtered range, counting from one. If the range starts in column C, Field 1 is column C, regardless of its worksheet column letter.
How do I remove all AutoFilter criteria before applying new ones?
When rows are actively filtered, use ShowAllData to show them, then set AutoFilterMode to False if you also want to remove the arrows. Check FilterMode before calling ShowAllData.
Can I use AutoFilter on an Excel Table?
Yes. Tables have built-in filter controls, and VBA can work with the table’s range or its ListObject fields. A table is a practical choice when records are frequently added because it can expand with the data.
Related
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
- Excel Text to Columns: How to Split Data Into Fields
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm ComboBox: Load a List with VBATo load an Excel UserForm ComboBox from worksheet values, add a ComboBox to the form, then fill it…
- Excel VBA Or Operator: How It Works and When to Use ItIn Excel VBA, the Or operator combines conditions and returns True when at least one condition is True.…
- VBA Code to Display a Graph on a UserFormUse an Excel chart on a worksheet as the graph source, export it as a JPEG, then load…
- Microsoft Excel VBA Dictionary: How to Use ItA Microsoft Excel VBA Dictionary is a key-and-value collection that lets a macro look up, count, and update…
- Excel VBA: Find the Last Row with Data in a ColumnTo find the last populated row in column A with Excel VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is…
- VBA Array Add: How to Append Values and Resize an ArrayVBA has no built-in Array.Add method. To add values, use a dynamic array and resize it with ReDim…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.