Excel 2003. I am using a VBA procedure to step through a worksheet
from bottom to top with a for-to loop, looking at the cell in column A
for each row. If column A contains different text than the previous
cell in column A, I insert a row about that one with text to indicate
what the numbers in each cell refer to. To speed up the process, I
use Application.Calculation=xlManual before starting the loop. As the
loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
Since the statusbar says "Calculate", I presume it really is
recalculating the sheet each time it inserts a row, thereby slowing
down my procedure. Is Excel really calculating like I think it is?
And, if so, how can I prevent this? Thanks.

Try putting this...

Application.EnableEvents = False

before you start your calculations and reset it with this...

Application.EnableEvents = True

after the calculations are done. However, you should probably include an On
Error trap and put that last statement in its code block just in case your
code "errors out" so that you are not left in a non-enabled state
afterwards.

--
Rick (MVP - Excel)


"Lucky" wrote in message
...
Excel 2003. I am using a VBA procedure to step through a worksheet
from bottom to top with a for-to loop, looking at the cell in column A
for each row. If column A contains different text than the previous
cell in column A, I insert a row about that one with text to indicate
what the numbers in each cell refer to. To speed up the process, I
use Application.Calculation=xlManual before starting the loop. As the
loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
Since the statusbar says "Calculate", I presume it really is
recalculating the sheet each time it inserts a row, thereby slowing
down my procedure. Is Excel really calculating like I think it is?
And, if so, how can I prevent this? Thanks.

I don't think Excel is calculating, but it sounds like its doing a screen
refresh.

Try putting
Application.screenupdating=false ' at the start

Application.screenupdating=true ' at the end

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Lucky" wrote in message
...
Excel 2003. I am using a VBA procedure to step through a worksheet
from bottom to top with a for-to loop, looking at the cell in column A
for each row. If column A contains different text than the previous
cell in column A, I insert a row about that one with text to indicate
what the numbers in each cell refer to. To speed up the process, I
use Application.Calculation=xlManual before starting the loop. As the
loop runs, Excel flashes "Ready" and "Calculate" in the statusbar.
Since the statusbar says "Calculate", I presume it really is
recalculating the sheet each time it inserts a row, thereby slowing
down my procedure. Is Excel really calculating like I think it is?
And, if so, how can I prevent this? Thanks.

Thanks, Rick and Charles. I did disable events and screen updating,
but to no avail. So, I guess this will just remain a mystery. I will
look at the calculation site.
Lucky

A last note on this. I looked at the calculation site, and it
mentioned turning off worksheet.enablecalculation, so I tried that.
That eliminated the problem. Thanks again!

Hi

I think excel is not evaluating the formula but re-constructing the
formula as every time you delete/insert a row, CELL ADDRESSES CHANGE.

:)

Hemant Hegde