VBA Excel Autofilter Question

[Ron Henze]

Is there a way to return filtered values directly to a variable without
having to step through the visible cells? For example, filtering on
column A and B reduces the filter options displayed in the column C drop
down. I want to use data values in column C dropdown list to generate
graphs. My stepping routine takes a long time (minutes, but now want
seconds...next it will be yesterday
Thanks Ron

 

[Dick Kusleika]

Ron

You can put the value of a range into a variant variable to create an array.

Sub GetFilteredData()

Dim vaFilter As Variant
Dim i As Long

With Sheet1
vaFilter = .Range("C2", .Range("C" & .Rows.Count).End(xlUp)).Value
End With

For i = LBound(vaFilter, 1) To UBound(vaFilter, 1)
Debug.Print vaFilter(i, 1)
Next i

End Sub

Starts at C2 to omit the header row. It only picked up visible cells when I
tested it, but I'm not sure why.