Can somebody help me with a slow macro problem.

[JeffMelton]

I have a macro that has suddenly slowed down to a crawl. I turned
screen updating off to watch it, and simple loops can be followed @
about 2 per second or so. I've tried it on several different computers
and the same thing happens. All calculations are done in the macro, so
calculating cells isn't a problem. This thing has slowed down from
maybe 20 secs or less to ten minutes. I really can't figure out whats
going on as all my other sheets do just like normal. Also, if I add a
test macro with a simple loop through cells it works fine as well. The
workbook isn't large. The code is in a form, could that be the issue?
Something else I've ust noticed is if I run the macro, and leave the
workbook open, and then run a different macro in a different worksheet,
it slows way down also.

I'm completely stumped.

 

[Jim Thomlinson]

Just to definitely rule out the usual culprets try adding the following lines
of code to see what difference they make... If they do not make any
difference then post your code so we can take a quick look see...

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
'your code here
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

 

[Dave Peterson]

If you insert/delete rows or columns and have done a print (or print preview),
then excel could be trying to determine where to put those dotted lines (showing
pagebreaks) for each insertion/deletion.

If you're in view|page break preview mode, you can have the same trouble.

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