Filter multiple criteria not working with numbers

[MeghaJ]

Dear All,
I refereed to one of the post in this forum and got below code for multiple criteria filetr

Sub aTest()
    Dim v As Variant
    
    v = Application.Transpose(Range("MyList"))
    Range("A1").Select
    Selection.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues
End Sub

This same code is not working if i have all numbers. It does not show any filtered value.

What could be the problem? This code works awesome if i just add some text in filetr condition and raw data.

please help.

 

[Peter_SSs]

Try changing this line

v = Split(Join(Application.Transpose(Range("MyList"))))

 

[Marcelo Branco]

Hi Peter,

Yes, it works with your suggestion. Curious that it works differently with numbers...

M.

 

[Peter_SSs]

Hi Peter,

Yes, it works with your suggestion. Curious that it works differently with numbers...

M.

Yes, despite the data being numerical, the array being passed in as criteria must be a String array. The Join/Split forces that conversion.

 

[Engr Warsi]

ADVERTISEMENT

Hi
I am using the above code, my one problem, it searches only EXACT strings suppose I want to filter out all the cells containing "cat" in any form like "I have a cat" or "you have a cat which is very cute".
If I use filter string in cell as *cat* then it works only for the last filter not all.
My filter substrings are
*commented on*
*fat*
*dog*
*cat*

but it works only for *cat*
I changed many times but I works only with the last of named range cell.
I hope you understand.

what should I do to use the wildcards?

Thank You!

 

[Engr Warsi]

I fixed it But I don't like the solution I worked out!
It uses loop
For LC = 1 To UBound(v)

Columns("A:A").Select
'Selection.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues
'Selection.AutoFilter Field:=1, Criteria1:=v, Operator:=xlOr
Selection.AutoFilter Field:=1, Criteria1:=v(LC), Operator:=xlFilterValues
'
Cells.Select
Range("A42").Activate
Selection.ClearContents
Next

LC is loop counter
v is array
I want a solution without loop (if it is faster since I have to operate on 10k+ cells in one go

Thanks

 

[MrIfOnly]

Edit: retracted...posted before last reply.

 

[ricorico]

THx to all, I finally got it to work. Much thx.