I cannot understand why this autofilter by array does not work

[Comedy]

I have a sub that will filter on criteria and then delete those rows, it's a sub so I can pass different filter criteria to it each time. I've decided to trim it down by passing an array instead of doing mnultiple separate filters on each row... but it fails.

I'm using excel 2007.

I've simplified the piece that fails to the below and thecode below that which works.

Sub autofilter_with_array_fails()
RemoveValue = "Array(""Value1"", ""Value2"", ""Value3"", ""Value4"", ""Value5"", ""Value6"")"
ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:=RemoveValue, Operator:=xlFilterValues
End Sub

Sub autofilter_with_array_works()
ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:=Array("Value1", "Value2", "Value3", "Value4", "Value5", "Value6"), Operator:=xlFilterValues
End Sub


To me they seem to be doing exactly the same thing. What am I doing wrong?

 

[pgc01]

Hi

In the second case you are using an array in Criteria1, which I think is what you want.
In the first case, you are not passing an array to Criteria1, you are using a string.

I'm not sure I understand exactly what you need, but maybe this is it?

Sub xautofilter_with_array_fails()
Dim RemoveValue As Variant

RemoveValue = Array("Value1", "Value2", "Value3", "Value4", "Value5", "Value6")
ActiveSheet.Range("A1").AutoFilter Field:=3, Criteria1:=RemoveValue, Operator:=xlFilterValues
End Sub

(which should work despite the name)

 

[Norie]

They aren't doing the same thing.

The first code is trying to filter on the string '"Array(""Value1"", ""Value2"", ""Value3"", ""Value4"", ""Value5"", ""Value6"")"'.

 

[Comedy]

I see now. Thank you very much!

 

[pgc01]

You're welcome. Thanks for the feedback.