Long macro run-time (Excel 2000)

Topic

New Reply

Attached is a print-out of my macro. Why would the two macros (Copy and Fill) and (PivotCleanUp) take so long to run? When I run this on my data that is 1400 rows it takes over two minutes. Thank you for your help.

Reply | Quote

Replies

With 1400 rows and 18 columns, you’re processing lots of cells. Perhaps you can speed up macro execution by turning recalculation off temporarily:

Application.Calculation = xlCalculationManual

At the end, turn it on again:

Application.Calculation = xlCalculationAutomatic

Reply | Quote

On occasions I have tried to track down which loops were taking a long time to see where to focus my efforts at recoding or rethinking the logic:

At the beginning of the code you can declare/define some variables:

    Dim sglStart As Single
    Dim sglEnd As Single
    Dim sMsg As String    

    sMsg = "Times of Loops"

Then before each loop you wish to test add the line:

    sglStart = Timer

And after each loop add the lines (change “Desc 1” to something meaningful):

    sglEnd = Timer
    sMsg = sMsg & vbCrLf & _
        Format(sglEnd - sglStart, "0.0") & " secs" & vbTab & _
        "Desc 1"

Then at the end of the code add a line like:

    MsgBox sMsg

To display the message indicating how long each loop took.

Here is an example with 2 loops

Sub TestMe()
    Dim sglStart As Single
    Dim sglEnd As Single
    Dim sMsg As String
    Dim x As Long
    sMsg = "Times of Loops"
    
    sglStart = Timer
    For x = 1 To 10000
        DoEvents
    Next
    sglEnd = Timer
    sMsg = sMsg & vbCrLf & _
        Format(sglEnd - sglStart, "0.0") & " secs" & vbTab & _
        "Desc 1"
    
    
    sglStart = Timer
    For x = 1 To 50000
        DoEvents
    Next
    sglEnd = Timer
    sMsg = sMsg & vbCrLf & _
        Format(sglEnd - sglStart, "0.0") & " secs" & vbTab & _
        "Desc 2"
    
    
    MsgBox sMsg
End Sub

After tracking down and changing, these lines can be removed from the code.

Steve

Reply | Quote

Also along the lines of what Hans suggested, try turning off screen updating with:
Application.ScreenUpdating = False
at the start of your macro and
Application.ScreenUpdating = True
at the end. That, too, will help speed things up.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote