traversing a filtered range (excel 2007)

Hi.

Here's what I am looking to achieve. I have a list of actions like
such:

Name action cost VAT

Alex hamburger 3.59 1.2
Alex potato chips 5.06 1.6
John hamburger 3.59 1.2
Alex payment -3.59 -1.2

I also have a cell that should display balance.
Now, what I want it to do is to display a calculational result over a
FILTERED range, where filter is set via standard GUI. I am assuming
formulas are of no use here (please correct me if I am wrong), so what
I need to know is how to most efficiently traverse a FILTERED range in
VBA.

Thank you for your advice.

Alex.

Also, how do I hook into the event of filter change?
thanks again.

Take a look at the SUBTOTAL function. You can do subtotals over the entire
range you select, or just the rows or columns that are not hidden. Look in
Excel help.

HTH,

Eric

"parahumanoid" wrote:

Hi.

Here's what I am looking to achieve. I have a list of actions like
such:

Name action cost VAT

Alex hamburger 3.59 1.2
Alex potato chips 5.06 1.6
John hamburger 3.59 1.2
Alex payment -3.59 -1.2

I also have a cell that should display balance.
Now, what I want it to do is to display a calculational result over a
FILTERED range, where filter is set via standard GUI. I am assuming
formulas are of no use here (please correct me if I am wrong), so what
I need to know is how to most efficiently traverse a FILTERED range in
VBA.

Thank you for your advice.

Alex.
.

The catch is that I don't know what lines will be filtered out. The
user determines that by setting filters' values.
Plus, I am not sure if autofilter works by giving lines a hidden value
or by some deeper internal means.