By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

How do I get filter values in vba?

P: 2
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
Apr 18 '07 #1
Share this Question
Share on Google+
3 Replies


SammyB
Expert 100+
P: 807
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
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
Apr 18 '07 #2

P: 2
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
Apr 19 '07 #3

SammyB
Expert 100+
P: 807
> 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
Apr 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.