Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Long macro run-time (Excel 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Long macro run-time (Excel 2000)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Long macro run-time (Excel 2000)

    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:

    <pre> Dim sglStart As Single
    Dim sglEnd As Single
    Dim sMsg As String

    sMsg = "Times of Loops"</pre>



    Then before each loop you wish to test add the line:
    <pre> sglStart = Timer</pre>


    And after each loop add the lines (change "Desc 1" to something meaningful):
    <pre> sglEnd = Timer
    sMsg = sMsg & vbCrLf & _
    Format(sglEnd - sglStart, "0.0") & " secs" & vbTab & _
    "Desc 1"</pre>


    Then at the end of the code add a line like:
    <pre> MsgBox sMsg</pre>


    To display the message indicating how long each loop took.

    Here is an example with 2 loops

    <pre>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</pre>


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

    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Long macro run-time (Excel 2000)

    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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •