Hi Bernie,

Thanks for the suggestion. I am now using the Subtotal function
instead of SUM and it seems to work fine.
I have also found out why the execution was taking longer in excel
2003. Excel 2003 evaluates the formulas on the sheet in a different
manner then in excel 2000. Even if we hide/unhide rows, the formulas
on the work sheet are recalculated which was not the case in excel
2000. So I just turned off the formulas while the filtering function
was working. i used Application.Calculation = xlCalculationManual and
once the filtering was over, I reset it to Application.Calculation =
xlCalculationAutomatic.

Thanks for all the help.

"Bernie Deitrick" wrote in message ...
Umesh,

Instead of using SUM, use SUBTOTAL. Look in help for the correct syntax,
but subtotal only operates on visible cells, so will return the correct sum
after filtering. You can place the formula after filtering, or simply don't
inlcude the formula's row in the filtered range.

HTH,
Bernie

"Umesh Bhanushali" wrote in message
...
Hi Bernie

Thanks for your reply. Even I had first decided to use the AutoFilter
method of Excel. However, there is one problem to this. I have a
summation row at the bottom of these 5000 rows which are going to be
filtered. The formula in this summation row has to change automatically
as I hide\unhide these rows. E.g. If all the 5000 rows are visible it
would be SUM of all the 5000 rows. However, if only 3000 rows are
visible, it will be SUM of only these 3000 rows. When I use AutoFilter,
this formula will not be automatically updated, and hence I have to go
row by row to see which rows are visible and add create a new formula
each time Filtering is done. I had previously ran across the issue of
formula length exceeding 1024 characters. However, before filtering, I
now Sort these rows using Excels 'Sort' method, and then I have
implemented a logic which will create a formula like SUM(D20:D3000),
depending upon the sorted group which is displayed. Hope I am clear
enough in my explanations.

Can you help me in knowing how can I make my formula react to
hidden\unhidden rows automatically as I use AutoFilter. Second thing is,
my row by row iteration may be slow. It takes about 30 seconds in Excel
2002, but why does the same piesce of code take 5 minutes in Excel 2003?

Any help would be appreciated.

Thanks,
Umesh



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!