1st the question. If I use Set rng = ActiveSheet.AutoFilter.Range and
then do this rngColumns = rng.Address I can extract the columns from
here with Instr(Left/Right/Mid) or whatever. What I want to know how
to do is a for/next to determine which of these columns has its
individual filter turned on....

What I am ultimately trying to create is an add-in type of thing that a
user can take their spreadsheet and use the autofilter as they normally
do. And then when the want, click a button that will take what ever
filters that are "on" and SELECT DISTINCT or a GROUP BY or something
that will provide unique records for those select filters.

An example using 5 columns:

A B C D E
1 Model Accy_# P/N Supplier Price
2 341 125 213 Guys 1.25
3 341 222 213 Guys 1.25
4 411 574 213 Guys 1.25
5 411 222 444 Bobs 5.75
6 580 574 213 Guys 1.25
7 580 746 444 Bobs 5.75
8 580 222 213 Bobs 1.25
9 580 125 516 Guys 2.25


So in this example if the user selected the filter in column C and
choose 213 they would get all the models and accy_#'s with that - but
if they just have that one filter on and click the button I want to
ignore everything else and make a single row with that P/N on it
without having to go Data/Advanced Filter/Copy to another location,
unique records only...

Or if they selected a supplier (e.g. Guys) and click the button I want
to show only P/N 213 & 516.... I expect this to get harder as I dig
into it, but this is the high level view of what I'm trying to do...

Thanks

-Steve

You could loop through the Filters collection to check which Filter has its
Filter on.

Sub test()
Dim rngFilter As Range

Set rngFilter = Sheet2.AutoFilter.Range

With Sheet2.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
MsgBox .Range.Columns(i).Column
MsgBox .Range.Columns(i).Address
End If
Next i
End With

End Sub



" wrote:

1st the question. If I use Set rng = ActiveSheet.AutoFilter.Range and
then do this rngColumns = rng.Address I can extract the columns from
here with Instr(Left/Right/Mid) or whatever. What I want to know how
to do is a for/next to determine which of these columns has its
individual filter turned on....

What I am ultimately trying to create is an add-in type of thing that a
user can take their spreadsheet and use the autofilter as they normally
do. And then when the want, click a button that will take what ever
filters that are "on" and SELECT DISTINCT or a GROUP BY or something
that will provide unique records for those select filters.

An example using 5 columns:

A B C D E
1 Model Accy_# P/N Supplier Price
2 341 125 213 Guys 1.25
3 341 222 213 Guys 1.25
4 411 574 213 Guys 1.25
5 411 222 444 Bobs 5.75
6 580 574 213 Guys 1.25
7 580 746 444 Bobs 5.75
8 580 222 213 Bobs 1.25
9 580 125 516 Guys 2.25


So in this example if the user selected the filter in column C and
choose 213 they would get all the models and accy_#'s with that - but
if they just have that one filter on and click the button I want to
ignore everything else and make a single row with that P/N on it
without having to go Data/Advanced Filter/Copy to another location,
unique records only...

Or if they selected a supplier (e.g. Guys) and click the button I want
to show only P/N 213 & 516.... I expect this to get harder as I dig
into it, but this is the high level view of what I'm trying to do...

Thanks

-Steve