VBA Code To Filter By Wildcard String Pattern

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.

Excel showing values to be filtered after applying wildcard pattern matching

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to filter by wild card text pattern

Step 2

Insert a module in which we will write a VBA code to filter the data by wildcard text pattern.

Insert a module in the VB editor

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

VBA code to apply filter on wildcard condition

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.

Result showing the data in column A is filtered by wildcard pattern matching

Hope this helped.

Share The Knowledge

Random Posts