Autofilter with wildcards in Array

[dbe594]

Sub myFilter()
Dim dic     As Object
Dim eleData As Variant
Dim eleCrit As Variant
Dim arrData As Variant
Dim vTst    As Variant

Set dic = CreateObject("Scripting.Dictionary")
vTst = Array("*ABC*", "*DEF*", "*GHI*", "*JKLM*", "*nn*")
With ActiveSheet
.AutoFilterMode = False
arrData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
For Each eleCrit In vTst
For Each eleData In arrData
If eleData Like eleCrit Then dic(eleData) = vbNullString
Next
Next
.Columns("B:B").AutoFilter Field:=1, Criteria1:=dic.Keys, Operator:=xlFilterValues
End With
End Sub

Basically, you need to read the column to be filtered into the array arrData.
The loops do the filtering using the arrays and produce an output you can use in the AutoFilter statement.
The AutoFilter statement can be whatever you need it to be but use dic.Keys as the criteria array.

Hi, I am using the above code to filter my data. Thank you!

I am finding an issue when there is no matching data the macro breaks to code. Can you insert a correct code response with message "None Found!"? I am not a coder, so if you could supply this it would be most helpful.

I also found if there is no header row it includes the first row of data whether it agrees with criteria or not. I can work around this, but would be nice to not have to create a header row. Thanks! :D -David

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub myFilter()
Dim dic     As Object
Dim eleData As Variant
Dim eleCrit As Variant
Dim arrData As Variant
Dim vTst    As Variant

Set dic = CreateObject("Scripting.Dictionary")
vTst = Array("*ABC*", "*DEF*", "*GHI*", "*JKLM*", "*nn*")
With ActiveSheet
.AutoFilterMode = False
arrData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
For Each eleCrit In vTst
For Each eleData In arrData
If eleData Like eleCrit Then dic(eleData) = vbNullString
Next
Next
If dic.Count = 0 Then
   MsgBox "Nothing found"
   Exit Sub
End If
.Columns("B:B").AutoFilter Field:=1, Criteria1:=dic.Keys, Operator:=xlFilterValues
End With
End Sub

If you want to use the Autofilter, then you need to have a header row.