Search for a column and filter 3 or more names with wildcards

[dpchheda]

Hi Friends, I request your help here for a code to search for a column using column name and filter data using wildcards. ABC, PQR, and XYZ are part of the same team but no other column to identify teams and therefore they need to be searched at once from the given data.

Below code was generated by a macro. I tried to tweak it but I am not able to get my head around it and its not working as planned.

Sub Filter_DC()
Cells.Find(What:="Name", LookAt:=xlWhole).Activate
Selection.AutoFilter
ActiveSheet.Range("A1:V1000").AutoFilter Field:=14, Criteria1:=Array("*ABC*", "*PQR*", "*XYZ*"), Operator:=xlFilterValues
Cells.Find(What:="Name").Select
End Sub

Please help.

 

[Fluff]

You can only filter with an array for exact matches, not partial matches.
How about

Sub dpchheda()
   Dim Fnd As Range, Cl As Range
   
   Set Fnd = Range("1:1").Find("Name", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Range(Fnd, Cells(Rows.Count, Fnd.Column).End(xlUp))
         If InStr(1, Cl, "abc", vbTextCompare) > 0 Or InStr(1, Cl, "pqr", vbTextCompare) > 0 Or InStr(1, Cl, "xyz", vbTextCompare) > 0 Then
            .Item(Cl.Value) = Empty
         End If
      Next Cl
      Range("1:1").AutoFilter Fnd.Column, .Keys, xlFilterValues
   End With
End Sub

 

[dpchheda]

Thanks Fluff. This is really helpful.

 

[Fluff]

You're welcome & thanks for the feedback