Get Autofilter options (VBA)

[ashish128]

Hi to all,

I didnt knew what to type to search and therefore am posting this
request.

Whenever we autofilter a range and select any field then we get a
dropdown list of unique entries in that field.

1. If there is only one item then how to get that.

eg.

A
5
5
5
5

If I autofilter on column A then it will only contain 5. How can I get
this value using VBA

2. how to get all of the the autofilter options?
eg.
A
1
2
3
1
2
3
1
2
3
if I autofilter on column A then the available options will be

A
1
2
3

howto get them using VBA.

Kindly advice.

With Regards

Ashish Sharma

 

[Pete_UK]

Here's one I posted the other day:

Sub uniques_from_A()
'
' 09/10/2007 by Pete Ashurst
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub


It will give you the list of unique values from column A of the
current sheet (assume heading is on row1 - you must have a heading)
and
sorted.


It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.


Hope this helps.


Pete

 

[ashish128]

Here's one I posted the other day:

Sub uniques_from_A()
'
' 09/10/2007 by Pete Ashurst
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

It will give you the list of unique values from column A of the
current sheet (assume heading is on row1 - you must have a heading)
and
sorted.

It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.

Hope this helps.

Pete










- Show quoted text -

Thanks pal but sorry to say that i have no heading.

 

[Pete_UK]

Well, put one in temporarily and delete it afterwards.

Pete