Bottom line: Learn how to apply text filters with VBA. Includes examples for filtering for multiple items, begins with, contains, wildcards, etc.
Skill level: Intermediate
Download the File
The Excel file that contains the code can be downloaded below. This file contains code for filtering different data types and filter types. Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.
Text Filters in Excel
When filtering a column that contains text, we can pick items from the filter drop-down menu list.
We can also use the Text Filters sub menu to create a custom filter. This gives us options for filter criteria that equals, does not equal, begins with, ends with, contains, or does not contain specific text. We can also use wildcard characters for these filters.
The following macro contains examples for different types of text filters. It's important to note that the Criteria parameter values are wrapped in quotation marks. The comparison operators are also included inside the quotes. Please see my post on The Ultimate Guide to Filters in VBA for more details on how to use the AutoFilter method and its parameters.
VBA Code Samples for Text Filters
The code in the box below can be copy/pasted into the VB Editor.
Sub AutoFilter_Text_Examples()
'Examples for filtering columns with TEXT
Dim lo As ListObject
Dim iCol As Long
'Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
'Set filter field
iCol = lo.ListColumns("Product").Index
'Clear Filters
lo.AutoFilter.ShowAllData
'All lines starting with .AutoFilter are a continuation
'of the with statement.
With lo.Range
'Single Item
.AutoFilter Field:=iCol, Criteria1:="Product 2"
'2 Criteria using Operator:=xlOr
.AutoFilter Field:=iCol, _
Criteria1:="Product 3", _
Operator:=xlOr, _
Criteria2:="Product 4"
'More than 2 Criteria (list of items in an Array function)
.AutoFilter Field:=iCol, _
Criteria1:=Array("Product 4", "Product 5", "Product 6"), _
Operator:=xlFilterValues
'Begins With - use asterisk as wildcard character at end of string
.AutoFilter Field:=iCol, Criteria1:="Product*"
'Ends With - use asterisk as wildcard character at beginning
'of string
.AutoFilter Field:=iCol, Criteria1:="*2"
'Contains - wrap search text in asterisks
.AutoFilter Field:=iCol, Criteria1:="*uct*"
'Does not contain text
'Start with Not operator <> and wrap search text in asterisks
.AutoFilter Field:=iCol, Criteria1:="<>*8*"
'Contains a wildcard character * or ?
'Use a tilde ~ before the character to search for values with
'wildcards
.AutoFilter Field:=iCol, Criteria1:="Product 1~*"
End With
End Sub
Filters & Data Types
The filter drop-down menu options change based on what type of data is in the column. We have different filters for text, numbers, dates, and colors. This creates A LOT of different combinations of Operators and Criteria for each type of filter.
I created separate posts for each of these filter types. The posts contain explanations and VBA code examples.
- How to Clear Filters with VBA
- How to Filter for Blank & Non-Blank Cells
- How to Filter for Numbers with VBA
- How to Filter for Dates with VBA
- How to Filter for Colors & Icons with VBA
The file in the downloads section above contains all of these code samples in one place. You can add it to your Personal Macro Workbook and use the macros in your projects.
Please leave a comment below with any questions or suggestions. Thanks! 🙂
Very useful codes.
Hi Jon,
I’m writing code to apply a filter which need to be an array of “contains” fields. For instance, the filter says only show values that contain *box* or *red* or *blue*.
This one works for single items:
‘Brokerage Cash Filter
Sheet3.Range(“$A$3:$DI$1000”).AutoFilter Field:=14, Criteria1:= _
“*JOHNSON*”
But when I try to include the asterisks in an array, it doesn’t work.
Sheet8.Range(“$A$3:$HN$1000”).AutoFilter Field:=15, Criteria1:=Array( _
“*SMITH*”, “*LIVINGSTON*”, “*SWASEY*”), Operator:= _
xlFilterValues
Any tips on how to make it work to filter multiple “contains” values?
Thanks,
Dave
Hey Dave,
Great question! There are a few ways to go about this. Unfortunately, I don’t believe we can use wildcards in the Array for the criteria. You could use the xlOr operator to specify two criteria with wildcards, but this is pretty limiting.
I do have an article on Reverse Partial Match Lookup on Multiple Items, which allows you to basically specify that list of items to find in each cell. This is a more manual approach with a helper column that contains a formula. However, once setup you could use VBA to filter that column for non-blank cells.
Another approach I’ve seen on the web is to build an array of all the matching items based on your wildcard list. Then filter the column for that array of items. This will be pretty labor intensive depending on how many rows are in your data set and how many filter items you have. You will be looping multiple times.
I hope that helps.
Hi Jon,
Am also facing with the same issue which Dave face. Jon could you please provide me with the code?
Dear Jon, do we have to write the names of the elements of the array manually? Or is there any option to retrieve the text entries from the worksheet?
Hi.. its very useful.
But I am getting run time error 9 : Subscript out of range
Set lo = Sheet1.ListObjects(1)
at above line. Can someone help me