Filtering by using collection as criteria is practical ??

[brkkrkmz112]

Hi,

Im trying to use autofilter to filter in columns by using the values that are stored in an array or collection as criteria . I already know it is ok with array but when ım trying that with collection I couldnt achieve my aim so far and ım just wondering it is possible or not ? Im sharing just a simple example below.

Thanks in advance.

Sub collec()


Dim fruits As New Collection


fruits.Add "orange"
fruits.Add "apple"


With ActiveSheet


.AutoFilterMode = False


.Range("G3:G6").AutoFilter field:=1, criteria:=fruits, Operator:=xlFilterValues


End With


End Sub

 

[KeepTrying]

Hi,

I'm not sure you can retrieve all items for a collection as you want for filtering. You can retrieve item of a collection by index number, e.g.:

Sub collec01()
Dim fruits As New Collection
fruits.Add "orange"
fruits.Add "apple"
With ActiveSheet
    .AutoFilterMode = False
    .Range("g3:g6").AutoFilter field:=1, Criteria[COLOR=#ff0000]1[/COLOR]:=fruits[COLOR=#ff0000].Item(1)[/COLOR], Operator:=xlFilterValues
End With
End Sub

This code above will filter "orange" only. I'd use Dictionary object instead:

Sub collec02()
Dim D As Object, i As Long
Set D = CreateObject("scripting.dictionary")
D.Add "o", "orange"
D.Add "a", "apple"
With ActiveSheet
    .AutoFilterMode = False
    .Range("g3:g6").AutoFilter 1, Criteria1:=D.items, Operator:=xlFilterValues
End With
End Sub

Here is a useful article about Collection vs Dictionary:
Using the Dictionary Class in VBA

Regards,

 

[SpillerBD]

I'm thinking you could use Advanced Filter as its criteria is an Array.

 

[brkkrkmz112]

So ı understand from your answer that I couldnt use collection as I use array or dictionary. Even if I d like to use collection Im supposed to describe the item that stored in the collection one by one. By the way thanks for the link.

Hi,

I'm not sure you can retrieve all items for a collection as you want for filtering. You can retrieve item of a collection by index number, e.g.:

Sub collec01()
Dim fruits As New Collection
fruits.Add "orange"
fruits.Add "apple"
With ActiveSheet
    .AutoFilterMode = False
    .Range("g3:g6").AutoFilter field:=1, Criteria[COLOR=#ff0000]1[/COLOR]:=fruits[COLOR=#ff0000].Item(1)[/COLOR], Operator:=xlFilterValues
End With
End Sub

This code above will filter "orange" only. I'd use Dictionary object instead:

Sub collec02()
Dim D As Object, i As Long
Set D = CreateObject("scripting.dictionary")
D.Add "o", "orange"
D.Add "a", "apple"
With ActiveSheet
    .AutoFilterMode = False
    .Range("g3:g6").AutoFilter 1, Criteria1:=D.items, Operator:=xlFilterValues
End With
End Sub

Here is a useful article about Collection vs Dictionary:
Using the Dictionary Class in VBA

Regards,

 

[Norie]

You need to use an for the criteria when you use the xlFilterValues operator.

 

[KeepTrying]

So ı understand from your answer that I couldnt use collection as I use array or dictionary. Even if I d like to use collection Im supposed to describe the item that stored in the collection one by one. By the way thanks for the link.

Exactly that's my view. You're welcome

 

[KeepTrying]

You need to use an for the criteria when you use the xlFilterValues operator.

Hi Norie,
Thanks for jumping in. It seems that a word is missing from your comment after "an" isn't it?

 

[Norie]

Oops, that should be 'an array'.

 

[KeepTrying]

Thank you