VBA Excel Autofilter Question
 

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

VBA Excel Autofilter Question
 

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Ron Henze wrote:
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!