Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get Autofilter options (VBA)
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get Autofilter options (VBA)
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 On Oct 11, 9:47 am, ashish128 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get Autofilter options (VBA)
On Oct 11, 5:47 pm, Pete_UK wrote:
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 On Oct 11, 9:47 am, ashish128 wrote: 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- Hide quoted text - - Show quoted text - Thanks pal but sorry to say that i have no heading. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get Autofilter options (VBA)
Well, put one in temporarily and delete it afterwards.
Pete On Oct 11, 4:39 pm, ashish128 wrote: On Oct 11, 5:47 pm, Pete_UK wrote: 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 On Oct 11, 9:47 am, ashish128 wrote: 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- Hide quoted text - - Show quoted text - Thanks pal but sorry to say that i have no heading.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
viewing data in autofilter options. | Excel Discussion (Misc queries) | |||
how do I add more toolbar options to my right click options | Excel Discussion (Misc queries) | |||
Autofilter Options | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) | |||
Autofilter list options | Excel Discussion (Misc queries) |