Yes. In Excel VBA, use * to match any number of characters, ? to match one character, and ~ to treat a wildcard as a literal character in an AutoFilter criterion. For example, to show rows where the second field contains “east,” use dataRange.AutoFilter Field:=2, Criteria1:="=*east*".
What AutoFilter criteria do
The AutoFilter method applies a filter to a rectangular range that includes a header row. Its Field argument identifies the column to filter, counted from the left edge of that range, not by the worksheet’s column letter. If the range begins in column C, for example, Field:=1 means column C and Field:=2 means column D.
A wildcard criterion lets VBA match text patterns rather than one complete value. This is useful for names, product codes, email addresses, descriptions, and other text that varies from row to row. The filter hides rows that do not match; it does not delete them or change their cell contents. You can therefore inspect, copy, or summarize the visible records while the other rows remain in the worksheet.
Include the header row in the range, and make sure the header is not counted as a data record. If the range is too narrow, the field number points at the wrong column; if it omits rows, those rows are not filtered. A worksheet table can make the data boundary easier to manage, but the meaning of Field is still relative to the range used for the filter.
What the wildcard characters mean
Excel’s AutoFilter criteria use the same basic wildcard characters as worksheet text matching. Put an equals sign before the pattern when expressing a text match in VBA; the examples below show that syntax inside Criteria1.
=North*matches text that begins with North, such as North, Northeast, or North depot.=*north*matches text containing north anywhere, such as North depot or the far north route.=*sonmatches text ending in son, such as Wilson or Addison.=?atmatches a three-character value whose last two characters are at, such as cat or hat. The question mark stands for exactly one character.=A??-*matches text beginning with A, followed by exactly two characters and a hyphen, with any remaining characters after it.
An asterisk can match zero or more characters, while a question mark matches one character only. A pattern such as =AB* can therefore match AB itself as well as AB followed by more text. Wildcard matching is generally not case-sensitive in Excel AutoFilter, so a pattern using north ordinarily also finds North. AutoFilter does not provide a simple wildcard option for case-sensitive matching.
Write a basic VBA wildcard filter
This example filters the second column of a data range for any text containing east. Adjust the worksheet name and range to match the workbook. The first row of the range must contain the column headings.
Sub FilterForEast()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:C100")
' Field 2 is the second column within dataRange.
dataRange.AutoFilter Field:=2, Criteria1:="=*east*"
End Sub
Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z. In the example, the field points to column B because the range starts in A. If the range instead started in B, the same field number would point to column C. The displayed filter arrow lets you see that a filter is active, but the actual criterion is set by the code.
If the sheet has other filters on the same range, calling AutoFilter for one field typically changes that field’s criterion while leaving the other field filters in place. For predictable results, use the same complete range each time rather than applying different AutoFilter ranges to overlapping data.
Combine wildcard conditions
Use Operator:=xlAnd or Operator:=xlOr when one field must meet either of two criteria. With AND, both conditions must match; with OR, either condition can match. For example, these conditions look for rows whose second-field text contains both north and depot:
Sub FilterForNorthDepot()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:C100")
' Both patterns must match the same field.
dataRange.AutoFilter Field:=2, _
Criteria1:="=*north*", Operator:=xlAnd, _
Criteria2:="=*depot*"
End Sub
Change xlAnd to xlOr if a match for north or a match for depot is sufficient. Criteria1 and Criteria2 both apply to the same field in this call. To filter different columns, call AutoFilter again for each field, using the same range and the appropriate field number. Excel’s standard AutoFilter interface offers at most two criteria for a single field in this form; for more involved logic, consider a helper column or Advanced Filter.
Match literal asterisks, question marks, and tildes
Because * and ? are special, use a tilde before one when you need to match the actual character instead. For example, the following pattern finds text containing a literal asterisk anywhere in the value:
dataRange.AutoFilter Field:=2, Criteria1:="=*~**"
Read the pattern after the equals sign as: any text, then a literal asterisk, then any text. To find a literal question mark, use ~? in the pattern; to match a literal tilde, use ~~. For instance, a criterion that finds text containing the literal characters *report* can be written as =*~*report~**. The outer asterisks allow other characters before and after; the tildes make the two inner asterisks literal.
This distinction is a frequent source of confusion: a criterion containing an unescaped asterisk does not search for a star symbol. It treats that character as a wildcard. If a pattern unexpectedly matches many rows, check whether a wildcard needs to be escaped or whether the pattern is broader than intended.
Know what wildcards will and will not match
Wildcards are intended for text matching. A number stored as a numeric value is not simply converted into text for a pattern such as =12*, and dates are stored as serial numbers rather than as the formatted text shown on the sheet. For numeric or date filtering, use comparisons such as greater than or equal to a date value, or filter by an explicit list of values, instead of relying on text wildcards.
Cells containing formulas can be filtered according to their calculated results. If a formula returns text, a text wildcard can match that result; if it returns a number, treat it as a numeric value. Formatting is not part of the wildcard pattern: a displayed date format or a cell’s color does not turn its underlying value into matching text. Also check for leading, trailing, or nonbreaking spaces when a value looks right but does not match. A wildcard may conceal some ordinary extra characters, but it cannot correct a wrong field, an unexpected value type, or data outside the filter range.
AutoFilter’s regular wildcard pattern is not a regular expression. It does not support regex character classes, alternation syntax, or quantifiers such as {2,4}. If a search needs those rules, use VBA code that checks each value or another approach designed for more complex text matching.
Fix common AutoFilter problems
- The wrong column filters: Recount
Fieldfrom the left edge of the exact range passed to AutoFilter. It is not the absolute worksheet column number. - No rows appear: Check that the pattern begins with
=, the header row is included, and the target field contains text rather than numbers or dates. Confirm that the intended records are inside the range. - Too many rows appear: Remember that
*matches zero or more characters. Use a more specific pattern, or escape an asterisk or question mark if it is meant literally. - Only some records are considered: A fixed range such as A1:C100 excludes data below row 100. Expand the range or use a table whose data range grows with added records.
- The macro errors when changing filters: Check the worksheet name, the range address, and whether the field number is valid for that range. Also confirm that the workbook is not protected in a way that prevents the operation.
Filtering a header-only range or a range with inconsistent headings can also produce confusing results. Keep one header row, avoid merged cells in the data area, and ensure that each column has a clear heading. If the worksheet already contains manually selected filters, inspect them before interpreting the visible rows as the complete result of the latest criterion.
Clear a filter or remove the arrows
To show all records while keeping the filter dropdown arrows, use ShowAllData only when the worksheet is currently filtered. The FilterMode check avoids calling it when no filter is active:
Sub ShowAllRows()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Clear active criteria but leave AutoFilter arrows in place.
If ws.FilterMode Then ws.ShowAllData
End Sub
To remove the AutoFilter arrows as well, set AutoFilterMode to False. That removes the worksheet’s AutoFilter display; it is different from merely clearing the current criteria. If a table owns the filter, use the table’s filter controls or its associated range as appropriate, rather than assuming that a worksheet-level change has the desired effect on every table.
Version and platform notes
These examples target desktop Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros, so run the code in desktop Excel. Excel for Mac can run VBA, but Microsoft documents the Range.AutoFilter method as unsupported on Mac; code that relies on this specific method may therefore need a different approach or a Windows desktop environment. Check the behavior in the exact Excel version and platform being used rather than assuming that a macro written for Windows will work unchanged on a Mac.
Frequently Asked Questions
How do I filter for text that contains a word in VBA?
Include wildcards on both sides of the word and use the pattern as the criterion, for example Criteria1:="=*east*". Set Field to the target column’s position within the filtered range.
What does the question mark do in an AutoFilter criterion?
A question mark represents exactly one character. Thus, =?at can match cat or hat, but not at or chat. Use an asterisk when the number of characters can vary.
How do I search for an actual asterisk in a cell?
Put a tilde before the asterisk so Excel treats it literally. To find a literal asterisk anywhere in a text value, use =*~** as the criterion.
Can I use wildcard criteria for numbers or dates?
Not reliably as text patterns. Numbers and dates are underlying numeric values, even when they are displayed with text-like formatting. Use numeric or date comparison criteria, or convert values to text deliberately if text matching is genuinely required.
How can I clear the filter without removing its dropdown arrows?
When the worksheet is filtered, call ws.ShowAllData. Check ws.FilterMode first so the macro does not call the method when there is no active filter. Set ws.AutoFilterMode = False only when you also want to remove the arrows.
Related
- Excel Solver Simplex: When to Use It and How It Works
- Excel Solver Reports: What They Show and How to Read Them
- Excel Solver: How to Save and Reuse Solver Scenarios
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Turn On AutoFilter in VBA: Code and ExamplesTo turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter…
- VBA Show UserForm: Open a Form in ExcelTo show an Excel VBA UserForm, call its Show method, usually from a standard module: UserForm1.Show. In Excel…
- Excel VBA: How to Center a Range HorizontallyTo center the contents of every cell in an Excel range horizontally with VBA, set the range’s HorizontalAlignment…
- VBA Sort Header: Keep Column Headings in Place When SortingTo keep a header row out of the sorted data in Excel VBA, set the sort option to…
- VBA Code to Delete a Row in ExcelTo delete a worksheet row with VBA, use Rows(5).Delete for row 5. In a macro, qualify the row…
- Excel VBA Pivot Charts: How to Create and Refresh ThemYes—VBA can refresh, filter, and manage Excel PivotCharts, and you can create one by selecting a PivotTable and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.