Filter with VBA

[elotromateo]

I have a ListBox in a UserForm with values that I wish to use to filter a range in a worksheet. I tried using .List to create an array to filter, since that is what it uses to filter, but it gives a type mismatch. Is the .List not really an array, or is it organized differently?

ActiveSheet.Range("$A$1:$P$42").AutoFilter Field:=1, Criteria1:=GraphList.List, _
     Operator:=xlFilterValues

 

[Norie]

It's a 2-dimensional array, and as far as I know it's only 1-dimensional arrays you can use for the criteria in AutoFilter.

You could try assigning the list to a variable and transposing it with Application.Transpose.

 

[elotromateo]

Ok, i figured it out. Just created a new array that started at 1 instead of 0 and it hasn't given me any problems.

Dim NArray() As Variant

    LenArr = 0
    
    For i = 0 To GraphList.ListCount - 1
        ReDim Preserve NArray(1 To LenArr + 1)
        LenArr = UBound(NArray) - LBound(NArray) + 1
        
        NArray(LenArr) = GraphList.List(i)
    Next
    
    
    ActiveSheet.Range("$A$1:$P$42").AutoFilter Field:=1, Criteria1:=NArray, _
        Operator:=xlFilterValues