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

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

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

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

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