Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Properties
Hi All
I am interested in discovering how I can access the autofilter object-properties (if at all?). I'll explain what I am trying to do.. If I set an autofilter on a worksheet with a column of related text, the drop-down appears in which the unique list of values in that column appears. Is there an object or property I can access from VBA for that list? In a similar vain, if I set a filter on the first column, any other columns autofilter drop down list is restricted to values for that column based on the rows selected by the first filter. Again I wish to access this list from VBA. TIA Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Properties
the list is not available through VBA.
-- Regards, Tom Ogilvy "Nigel" wrote in message ... Hi All I am interested in discovering how I can access the autofilter object-properties (if at all?). I'll explain what I am trying to do.. If I set an autofilter on a worksheet with a column of related text, the drop-down appears in which the unique list of values in that column appears. Is there an object or property I can access from VBA for that list? In a similar vain, if I set a filter on the first column, any other columns autofilter drop down list is restricted to values for that column based on the rows selected by the first filter. Again I wish to access this list from VBA. TIA Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Properties
Maybe you could use the same technique that John Walkenbach does to fill a
listbox with unique entries: http://j-walk.com/ss/excel/tips/tip47.htm And you could use the same kind of thing for the secondary columns. Just loop through the visible cells in that column. Nigel wrote: Hi All I am interested in discovering how I can access the autofilter object-properties (if at all?). I'll explain what I am trying to do.. If I set an autofilter on a worksheet with a column of related text, the drop-down appears in which the unique list of values in that column appears. Is there an object or property I can access from VBA for that list? In a similar vain, if I set a filter on the first column, any other columns autofilter drop down list is restricted to values for that column based on the rows selected by the first filter. Again I wish to access this list from VBA. TIA Nigel -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Properties
Thanks Dave, great tip. I used a collection to build the unique list as
suggested by John Walkenbach. The collection properties yields the stuff I needed. Having used an autofilter I wrapped the Collection.Add with Cell.EntireRow.Hidden to only fill the collection with the visible rows after applying the filter. It works great! 'Code snippet follows ' This example is based on a tip by J.G. Hussey, ' published in "Visual Basic Programmer's Journal" ' adpated by John Walkenbach Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection ' The items are in A2:A106, autofilter applied to column A Set AllCells = Range("A2:A106") On Error Resume Next For Each Cell In AllCells If Not Cell.EntireRow.Hidden Then NoDupes.Add Cell.Value, CStr(Cell.Value) End If Next Cell ' collection NoDupes contains non duplicated, filtered items in chosen range only Cheers Nigel "Dave Peterson" wrote in message ... Maybe you could use the same technique that John Walkenbach does to fill a listbox with unique entries: http://j-walk.com/ss/excel/tips/tip47.htm And you could use the same kind of thing for the secondary columns. Just loop through the visible cells in that column. Nigel wrote: Hi All I am interested in discovering how I can access the autofilter object-properties (if at all?). I'll explain what I am trying to do.. If I set an autofilter on a worksheet with a column of related text, the drop-down appears in which the unique list of values in that column appears. Is there an object or property I can access from VBA for that list? In a similar vain, if I set a filter on the first column, any other columns autofilter drop down list is restricted to values for that column based on the rows selected by the first filter. Again I wish to access this list from VBA. TIA Nigel -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Properties
One might want to consider checking the reference to Microsoft Scripting
Runtime and using a Dictionary Object rather than a Collection; more versatile and, I believe, significantly faster, primarily because the entire set of data can be transferred from the Dictionary directly without looping. Alan Beban Nigel wrote: Thanks Dave, great tip. I used a collection to build the unique list as suggested by John Walkenbach. The collection properties yields the stuff I needed. Having used an autofilter I wrapped the Collection.Add with Cell.EntireRow.Hidden to only fill the collection with the visible rows after applying the filter. It works great! 'Code snippet follows ' This example is based on a tip by J.G. Hussey, ' published in "Visual Basic Programmer's Journal" ' adpated by John Walkenbach Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection ' The items are in A2:A106, autofilter applied to column A Set AllCells = Range("A2:A106") On Error Resume Next For Each Cell In AllCells If Not Cell.EntireRow.Hidden Then NoDupes.Add Cell.Value, CStr(Cell.Value) End If Next Cell ' collection NoDupes contains non duplicated, filtered items in chosen range only Cheers Nigel "Dave Peterson" wrote in message ... Maybe you could use the same technique that John Walkenbach does to fill a listbox with unique entries: http://j-walk.com/ss/excel/tips/tip47.htm And you could use the same kind of thing for the secondary columns. Just loop through the visible cells in that column. Nigel wrote: Hi All I am interested in discovering how I can access the autofilter object-properties (if at all?). I'll explain what I am trying to do.. If I set an autofilter on a worksheet with a column of related text, the drop-down appears in which the unique list of values in that column appears. Is there an object or property I can access from VBA for that list? In a similar vain, if I set a filter on the first column, any other columns autofilter drop down list is restricted to values for that column based on the rows selected by the first filter. Again I wish to access this list from VBA. TIA Nigel -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |