identical macros run fast and slow

[markwattwood]

I have written two identical Macros using Visual Basic. The only difference
in the code are the variables that are inputted. the code contains
directions that cause lines to be hidden or cells to be erased based upon
either the color or content of the cells. One macro runs fast but the other
runs very slow. Ive contacted IBM and they say it is not the processor.
When viewing the code in the debug mode the selection.clearcontents seems to
cause a pause in the execution but only in the one macro.

 

[Dave Peterson]

If you're hiding rows or columns, then excel may be trying to determine where
those little dotted lines go (the ones you see after you do File|print preview).

If you delete/hide/show lots of rows, this can slow your macro down.

As a test, try this on the slow version.

Open your workbook
tools|options|view tab|uncheck Page Breaks

Then run your macro. If it works fast, then maybe you found the problem.

===
Saved from a previous post...


There are some other settings that can make your code work faster:

Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False


The first stops the screen flickering when you select different sheets (but it's
even better to not select sheets/ranges/objects).

The second & third line turns calculation to manual (and remembers the current
setting).

You'll want to turn it back to what it was before you started at the end:
Application.calculation = calcmode

And the last tells excel to not worry about where to show those dotted lines
(where page breaks would be). This is useful if your macro inserts/deletes rows
or columns.

 

[markwattwood]

If you're hiding rows or columns, then excel may be trying to determine where
those little dotted lines go (the ones you see after you do File|print preview).

If you delete/hide/show lots of rows, this can slow your macro down.

As a test, try this on the slow version.

Open your workbook
tools|options|view tab|uncheck Page Breaks

Then run your macro. If it works fast, then maybe you found the problem.

===
Saved from a previous post...


There are some other settings that can make your code work faster:

Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False


The first stops the screen flickering when you select different sheets (but it's
even better to not select sheets/ranges/objects).

The second & third line turns calculation to manual (and remembers the current
setting).

You'll want to turn it back to what it was before you started at the end:
Application.calculation = calcmode

And the last tells excel to not worry about where to show those dotted lines
(where page breaks would be). This is useful if your macro inserts/deletes rows
or columns.

 

[markwattwood]

I checked the view bar and you are right. the page breaks is not checked in
one, but in the slow one it is in grey and will not uncheck. Ive unprotected
the sheet . All other settings are identical to the fast macro.

 

[Dave Peterson]

It sounds like you're viewing the window in Page Break Preview mode.

View|Normal

And with that added code, it should work quicker.

 

[markwattwood]

right again
thanks

It sounds like you're viewing the window in Page Break Preview mode.

View|Normal

And with that added code, it should work quicker.