cycling through all the possible criteria in an autofilter column
 

I have an autofiltered list of data, that currently allows the user to
select the client from the list, then there are subtotals at the bottom,
which I use as the basis for further analysis. So my boss can see each
clients data but only one at a time.
What I want to be able to do, is via code select each possible client from
the list, apply that to the autofilter, then do the existing calcs, and move
on to the next client, do the calcs, store the data one column over, etc to
the bottom of the list.

The bit I can't figure out is how to get the list of unique clients that the
autofilter shows when one uses it manually.
Granted I could sort the data by client, create an array, run down the list
adding the next client, and create my own list; but surely the list Excel
shows must be accessable somehow.

TIA

cycling through all the possible criteria in an autofilter column
 

Graham,

What you are describing can be done automatically using pivot tables.

OR

To get the list of unique values, use data / filter... / advanced filter / unique values only, copy
to another location.

HTH,
Bernie
MS Excel MVP


"Graham Y" wrote in message
...
I have an autofiltered list of data, that currently allows the user to
select the client from the list, then there are subtotals at the bottom,
which I use as the basis for further analysis. So my boss can see each
clients data but only one at a time.
What I want to be able to do, is via code select each possible client from
the list, apply that to the autofilter, then do the existing calcs, and move
on to the next client, do the calcs, store the data one column over, etc to
the bottom of the list.

The bit I can't figure out is how to get the list of unique clients that the
autofilter shows when one uses it manually.
Granted I could sort the data by client, create an array, run down the list
adding the next client, and create my own list; but surely the list Excel
shows must be accessable somehow.

TIA

cycling through all the possible criteria in an autofilter col
 

Thanks Bernie, I'm not sure I could do it with pivot tables, as in my result
space I have to allow for some 'extra' income to be added manually. But that
is an interesting idea.
But the advanced filter tip would work, Thanks.

"Bernie Deitrick" wrote:

Graham,

What you are describing can be done automatically using pivot tables.

OR

To get the list of unique values, use data / filter... / advanced filter / unique values only, copy
to another location.

HTH,
Bernie
MS Excel MVP


"Graham Y" wrote in message
...
I have an autofiltered list of data, that currently allows the user to
select the client from the list, then there are subtotals at the bottom,
which I use as the basis for further analysis. So my boss can see each
clients data but only one at a time.
What I want to be able to do, is via code select each possible client from
the list, apply that to the autofilter, then do the existing calcs, and move
on to the next client, do the calcs, store the data one column over, etc to
the bottom of the list.

The bit I can't figure out is how to get the list of unique clients that the
autofilter shows when one uses it manually.
Granted I could sort the data by client, create an array, run down the list
adding the next client, and create my own list; but surely the list Excel
shows must be accessable somehow.

TIA