Filtering data using an array and the xlor operator

[MarkWP]

I am trying to apply a filter to a record set. I have a column which contains data in the format

000000000000
001001000010
F02433101010
BB0001103000
BBB34F000000
342100100000
312101100000
001230000000
512222300010
BB2221113010
5BB342300000
6B2231000000
BB0234440000

What I need to do is show only the records which contain F, B, 3, 4, 5, or 6.

What I have tried.

varArray = Array("=*F*", "=*B*", "=*3*", "=*4*", "=*5*", "=*6*")
ActiveSheet.Range("$A$1:$P$63355").AutoFilter Field:=6, Criteria1:=Application.Transpose(varArray), Operator:=xlFilter

This does not give me the correct results. If I use the following I get the correct results with the exception it does not show me the values which contain 3, 4, 5, or 6.
ActiveSheet.Range("$A$1:$P$63355").AutoFilter Field:=6, Criteria1:="=*F*", Operator:=xlOr, Criteria2:="=*B*"

I think the xlFilter is using and instead of or which gives an incorrect result. Any idea's how to solve this?

Mark Perrigon
Cyber Corp Software
http://www.cybercorpsoftware.com

[xladept]

Hi Mark,

Maybe:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code into the white space on the right

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[MarkWP]

Thanks for the advice, unfortunately that's not really what I had in mind. My question was more directed towards using the auto filter and criteria array. It does not appear that you can be done using the auto filter, does anyone know the reasoning behind Microsoft limiting the Auto Filter criteria to two? Even beyond that why they wouldn't give me the option of setting the operator to XlOr instead of xlFilter when using the criteria array, I would think this could be done easily enough. I'm just kind of surprised that this limitation still exists.

[xladept]

Hi MarkWP,

I guess that you don't want to use the VBA - but, I believe that it does what you want to have done.