Wild card unable to use in autofilter array

[suresh7860]

Hi Experts

I am getting the results correctly when i use directly without wildcards (*) for contains but when i use wildcard (*) in array its not working, would be helpful if any of you can help me or guide me to correct it.

After this i have code to cut n move the data to another sheet which is working fine need to fix this to complete my work.

Dim vTst As Variant
vTst=Array("ABC", "DEF", "GHI", "JKLM") - Working fine (without contains - exact match -working fine)
vTst = Array("*ABC*", "*DEF*", "*GHI*", "*JKLM*") - (with contains - not working)

With Worksheets("Sheet")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion

'filter on all the values in the array

.AutoFilter Field:=2, Criteria1:=vTst, Operator:=xlFilterValues
(working for exact match and not working when wild card (*) used
or
.AutoFilter Field:=2, Criteria1:="=*" & vTst & "*", Operator:=xlFilterValues - wild cards not working

Thanks for your time experts

Regards
Suresh7860

 

[Mohammad Basem]

Hi Suresh7860,

You can have wildcards for 2 array elements only. E.g. Array("*ABC*", "*DEF*") or Array("*GHI*", "*JKLM*").

I think this is because the autofilter allows for 2 values in the Contain option.

 

[RickXL]

Mohammad is right but you can use VBA to construct a list of the names that should be found then you can use that instead.

For instance, say you had a list of states in the USA and you wanted to filter on "*nn*".
Instead you could:
1. Work out the list of states (Connecticut, Minnesota, Pennsylvania and Tennessee);
2. Create an array with that list in it;
3. Use that list in the AutoFilter.

Here is an example that reads in column B into an array.
It then loops round that data and an array with the selection string in it.
Adds all the matches to a Dictionary object
Uses the Dictionary in place of an array as the selection criteria.
Note: You can have as many selection strings as you want in the array.

Sub myFilter()
    Dim i       As Long
    Dim j       As Long
    Dim dic     As Object
    Dim arrEle  As Variant
    Dim arrData As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        .AutoFilterMode = False
        arrData = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        For Each arrEle In Array("*ee*", "*rk*", "*z*", "*J*")
            For j = 1 To UBound(arrData)
                If arrData(j, 1) Like arrEle Then dic(arrData(j, 1)) = vbNullString
            Next
        Next
        .Columns("B:B").AutoFilter Field:=1, Criteria1:=dic.Keys, Operator:=xlFilterValues
    End With
End Sub

My result was:

Excel 2013
	A	B	C	D
1	Abbreviation	State Name	Capital	Became a State
4	AZ	Arizona	Phoenix	February 14, 1912
5	AR	Arkansas	Little Rock	June 15, 1836
31	NJ	New Jersey	Trenton	December 18, 1787
33	NY	New York	Albany	July 26, 1788
43	TN	Tennessee	Nashville	June 1, 1796
Sheet1

 

[suresh7860]

Thank you Mohammad Basem but i have 40-50 which needs to be checked if i want to use more arrays then how to use them in criteria1 field?

 

[suresh7860]

I am new with this will try to work as per this and see what i can do or else will have to try it in other way entire code. thanks RickXL

 

[Mohammad Basem]

Hi Suresh7860,

…but i have 40-50 which needs to be checked if i want to use more arrays…

Do you mean that you have 40-50 conditions to check or 40-50 arrays? Could you please explain this point?

 

[RickXL]

This is slightly closer to your original post, it might help:

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.

 

[suresh7860]

Awesome RickXL, it worked superbly now i am able to run full code without this issue thanks a ton and have a wonderful day !!

 

[suresh7860]

Thanks Mohammad Bhai it worked from Rickxl Code

 

[RickXL]

I am pleased that it is working for you.

Thank you for letting me know.

Regards,