Autofilter Properties

[Nigel]

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

 

[Tom Ogilvy]

the list is not available through VBA.

 

[Dave Peterson]

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]

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

 

[Alan Beban]

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

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