Autofilter with wildcards in Array

dbe594

New Member
Joined
Aug 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi & welcome to MrExcel.
How about
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top