Excel bogs down when running my macros, what is causing it?

R

Rakmup

It doesn't happen in all workbooks. I have nearly identical workbooks and
some are slow to respond and process the macros, while others fly through in
a couple of seconds. I thought it was size related when I 1st encountered
the problem, but since I have had the problem on smaller workbooks. Setting
the calculation to manual seems to help, but does not eliminate the problem.
Looking for suggestions
 
D

Dave Peterson

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

(Saved from a previous post)

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.
 
Top