Autofilter with array

[pavanai]

I am trying to do an autofilter with an array and encountering " Error: AutoFilter method of Range Class failed "

Here is the vba code:

Dim sArray() as Variant

sArray = Array("MS*", "<>*ML*")
ActiveSheet.Range("A1:Q" & LastRow).AutoFilter Field:=3, Criteria1:=sArray, Operator:=xlFilterValues

Code works if i replace <>*ML" with any other value.. Any idea whats wrong?

 

[jasonb75]

What happens with

ActiveSheet.Range("A1:Q" & LastRow).AutoFilter Field:=3, Criteria1:="<>*ML*"

You don't need an array for the example provided, anything equal to MS* will be <>*ML* so the first part is redundant.

 

[Fluff]

Hi & welcome to MrExcel.
How about

 ActiveSheet.Range("A1:Q" & LastRow).AutoFilter Field:=3, Criteria1:="MS*", Operator:=xlOr, Criteria2:="<>*ML*"

 

[pavanai]

Thanks for the response. I added MS* for debugging. I basically wanted to add 2 "does not contain" conditions in my array and then use that array in the filter. Vba is not letting me do that.

 

[JoeMo]

Thanks for the response. I added MS* for debugging. I basically wanted to add 2 "does not contain" conditions in my array and then use that array in the filter. Vba is not letting me do that.

The array in your OP does not reflect 2 "does not contain" conditions. What are the 2 conditions you want to use?

 

[Fluff]

You should really only use an array for exact matches, if you only have two criteria, do like I showed in post#3

 

[pavanai]

The array in your OP does not reflect 2 "does not contain" conditions. What are the 2 conditions you want to use?

<>MS* and <>ML*

 

[jasonb75]

As far as I'm aware, you can't use 'does not contain' as part of an array, you can only use the method that Fluff has shown you. An array is only suitable for a list of 'equal to' items (which can use wildcards).

 

[Fluff]

Do you want does not Contain, or does not start with?

 

[JoeMo]

You can use an array for 2 "does not contain" conditions, but it's not necessary.

This will work:

Dim sArray() As Variant
sArray = Array("<>data10", "<>*ML*")
ActiveSheet.Range("A1:Q" & LastRow).AutoFilter Field:=3, Criteria1:=sArray(0), Operator:=xlAnd, Criteria2:=sArray(1)