How do I get filter values in vba?

Hello all,
I am using Excel 2002.
I have a list of data in coloumn A.
I have applied autofilter on that list
and now I am interested in getting the values
that appear when clicking the filter arrow, in vba.

Can anyone help me?

Thanks in advance.

v

To get all of the current Autofilter:
Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible)

To get just the values in column A:
Range(Cells(2, 1), Cells(2, 1).End(xlDown)).SpecialCells(xlCellTypeVisible)

So, for example
HTH --Sam

To get all of the current Autofilter:
Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible)

To get just the values in column A:
Range(Cells(2, 1), Cells(2, 1).End(xlDown)).SpecialCells(xlCellTypeVisible)

So, for example

Expand|Select|Wrap|Line Numbers

1.     Dim c As Range
2.     For Each c In Range(Cells(2, 1), Cells(2, 1).End( xlDown )).SpecialCells(xlCellTypeVisible).Cells
3.         MsgBox c.Value
4.     Next c

HTH --Sam

Hello Sam,
First, thank you for your reply.

I tried the code you sent and it turns out that it produces all of the
values in the column A.
What I need is the list of values without repeating themselves.
I hope it is clear.

thanks
v

> it produces all of the values in the column A.

Not if the auto-filter is on and there is a blank cell at the end of the filtered data. See http://www.thescripts.com/forum/thread634286.html for more help. If you record a macro, turning on the AutoFilter & doing the manual steps in that post, you can see what code you are missing. HTH --Sam