Using collection for autofiltering

[mkjaer]

Hi guys

I have generated a list of unique values in a Collection that I'm now unsuccessfully trying to use as AutoFilter Criterias. Anyone knows how to do this?

I have generated the list like this (only the relevant part :-)):

Dim myRow As Integer
Dim myRange As Range
Dim myCount As Integer

Range("B1").Select
Selection.End(xlDown).Select
myRow = ActiveCell.Row
Set myRange = Range("B2:B" & myRow)
myCount = myRange.Count


Dim myCol As New Collection
Dim myItem
Dim i As Long
Dim myCellVal As Variant

For i = 1 To myCount
myCellVal = Range("B" & i + 1).Value
On Error Resume Next
myCol.Add myCellVal, Chr(34) & myCellVal & Chr(34)
On Error GoTo 0
Next i

And am now trying to apply the filter like this:

ActiveSheet.Range("$A$1").AutoFilter Field:=25, Criteria1:=???

I have tried to use myItem, myCol and myCellVal (both as is and with an (i), e.g. myItem(i), replacing the ??? above.

Anyone who knows what I'm doing wrong :-)?

Thanks

[Bernie Deitrick]

I don't know how to use a collection, but I do know how to use an array created from a collection:

Please Login or Register  to view this content.

[mkjaer]

Thanks a lot :-)!

Got it to work basically just by replacing my script with yours, only adding a few adjustments (needed the autofilter applied in a different sheet) so it was just perfect :-).