Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Faster code (Excel 2003 VBA)

    Very long code fragment (over 3,700 characters) moved to attachment by HansV

    I just finished the code below. I'm stil kinda new at this code stuff. The code runs a little slow for large datasets. Just wondering if someone could have a look at it and make any suggestions for improvements.

    Thanks

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

    Re: Faster code (Excel 2003 VBA)

    You should avoid selecting cells where possible. You can omit the loop to calculate Counter by using the End function. Try the attached version on a copy of your workbook.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster code (Excel 2003 VBA)

    That's it, I thought there would be more.

    Thanks,

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

    Re: Faster code (Excel 2003 VBA)

    You could speed things up a bit by eliminating a superfluous save of your 'NewBook'. Instead of:
    Set NewBook = Workbooks.Add
    With NewBook
    .SaveAs Filename:=reqformat
    End With
    and, later:
    Workbooks(reqformat).Save
    use:
    Set NewBook = Workbooks.Add
    and replace all subsequent instances of:
    Workbooks(reqformat)
    with:
    NewBook
    and, only when when you want to close, use:
    NewBook.SaveAs Filename:=reqformat

    If either of your 'NewBook' or file 'Clean Up ICP Data.xls' have formulae, I'd also suggest adding:
    Application.Calculation = xlManual
    to the beginning of your code - about where you have the line:
    Application.ScreenUpdating = False
    and the line:
    Application.Calculation = xlAutomatic
    to the end of your code - about where you have the line:
    Application.ScreenUpdating = True
    The extra lines turn off background recalcs while the macro executes. If your 'NewBook' acquires any formulae, you might then also want to add the line:
    NewBook.Sheets("Sheet1").Calculate
    before saving it.

    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
  •