get values from autofilter options

[sipron]

Hi
I have two columns. When I autofilter column A I would like to get the values shown in the autofilter in column B.

I don't want to loop through the entire sheet since there are a lot of items and i have to loop them a lot of times.

Does anyone know a solution to this?

Best regards
John

 

[Gary McMaster]

Hi John,

Maybe "SpecialCells" will do it.

Something like this:

Public Sub Test()

Dim oRange As Range

Set oRange = ActiveSheet.Range("B2:B" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)

MsgBox oRange.Address

End Sub

Gary

 

[sipron]

Thank you Gary.
This however will return the entire row of filtered results with a lot of copies. Is it possible to get just one copy of each item?

 

[Gary McMaster]

John,

Maybe the "AdvancedFilter" method is what you need instead of Auto Filters. AdvancedFilter can return unique values. Please see VBA help for that topic.

Can you provide a better description or examples of exactly what you are trying to do?

Gary