Subtotal Function response is slow

[GM]

hi,

I have one application, where data is being selected from database using sql
and then data is put in 3 excel worksheets. once data is copied from
recordset, I am doing some formatting and using excel subtotal function in a
loop for 3 worksheets. I have observed that subtotal for 1 worksheet is
completing in 4 secs and for 2 nd worksheet it takes 11 sec and for 3rd
worksheet it takes 20 sec.

Is there any way to improve this performace. If takes 4 sec for first
worksheet, i am not sure why it is taking more time for subsequent worksheets?

any help in this matter is highly appreciated.
GM

 

[Jim Thomlinson]

The subtotal function is just plain slow. It has to insert rows and such so
there is no way around it. Why not use a pivot table instead. You can hook it
directly to your data source with your existing SQL. You will not have to
fill 3 worksheets with data. You will get all of your subtotals. It is just a
much cleaner solution and lightning fast in comparison.

Just my 2 cents.