In this post we will see how to filter a column by applying wildcard text pattern criteria.
Suppose we have a range in excel with values in it and we would like to filter it by two or more wildcard text pattern, we can achieve the filtering result by using “*” as wildcard operator in VBA code.
Below is the data which shows the range of values in which we want to apply the wildcard filtering criteria, we want to filter all the rows that has “Mark” or “David” in it i.e. row number 2,4 and 6.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to filter the data by wildcard text pattern.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub Filetr_Widlcard()
With ActiveSheet.Cells(1, 1).CurrentRegion
.AutoFilter Field:=1, Criteria1:=”=*Mark*”, Operator:=xlOr, Criteria2:=”=*David*”
End With
End Sub
This vba code is specifying two wildcard criteria i.e. “*Mark*” and “*David*” which basically tells the VBA to look for rows that has “Mark” or “David” in it, * denotes 0 or more characters.
Now run the code by pressing F5 or by pressing play button the editor.
Below is the result that we get after running the code, as you can see in the result two wildcard text pattern filter is applied.
Hope this helped.