+ Reply to Thread
Results 1 to 6 of 6

VBA code to fix .select to speed up the macro

  1. #1
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    VBA code to fix .select to speed up the macro

    Hello,
    I have some code and I wanted to get a few opinions on if I could get rid of the .select functions and replace the code lines with range.clearcontents or range.select. I just want to do this in order to speed up the run time, due to the size of my entire workbook.

    Heres a portion of my code:
    Please Login or Register  to view this content.
    The current code runs well, just very slow.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA code to fix .select to speed up the macro

    Untested in the absence of the workbook, but try:
    Please Login or Register  to view this content.
    Last edited by Richard Buttrey; 07-17-2019 at 11:59 AM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: VBA code to fix .select to speed up the macro

    What module contains this code?
    What is the ActiveSheet when this code is run?

    It's almost always better to attach your file rather than snippets of code because the context is important.

    I also suggest you use
    Please Login or Register  to view this content.
    at the beginning then set back to True at the end. Even without the Selects, screen updates take a lot of time.

    Eliminating the Selects may speed things up a little but that may not be the main problem. It's possible your array formulas are sucking up a lot of calculate time.

    In general you can change things like this
    Please Login or Register  to view this content.
    to this

    Please Login or Register  to view this content.
    but I didn't take the (large amount of) time to edit all your code.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    05-09-2019
    Location
    St. Louis, USA
    MS-Off Ver
    2013
    Posts
    9
    2 other lines that speed up code are these

    Please Login or Register  to view this content.
    Then after the code, set the EnableEvents to True can and Calculation to xlCalculationAutomatic.

    However you should not use the first of you are need to trigger any events such as Worksheet_Change, and the second if you need calculations to be updated in the middle of your code. Try these and test it to be sure it still does what you want. But these two lines along with the screen updating have saved me so much time.
    Last edited by runfunke; 07-17-2019 at 01:02 PM.

  5. #5
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: VBA code to fix .select to speed up the macro

    Thank You Richard, it keeps giving me an error on the following line. Also can you explain why you used the periods (.)
    Please Login or Register  to view this content.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: VBA code to fix .select to speed up the macro

    Quote Originally Posted by Tgbell View Post
    Thank You Richard, it keeps giving me an error on the following line. Also can you explain why you used the periods (.)
    Please Login or Register  to view this content.
    Looks like a typo. Remove the . on the first line, and on the second line the initial - should be a .
    Please Login or Register  to view this content.
    When you use a With statement, the object specified does not have to be repeated as a qualifier within the With scope. That is, this code
    Please Login or Register  to view this content.
    Can be rewritten as
    Please Login or Register  to view this content.
    The dots were there to start with, but with the With you can remove the object from in front of the dots. This has a speed advantage for complex code because VBA does not have to recalculate the address of the object every time it's used; it calculates it once and then uses the same address throughout the With scope.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to speed up macro via formula or code (Countifs)
    By nickytraps in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-04-2018, 03:06 PM
  2. Reduction of code lines to speed up macro
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2016, 11:00 AM
  3. [SOLVED] Reduction of code lines to speed up macro
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2016, 11:00 AM
  4. How to improve the running speed of this VBA macro code?
    By sellim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 01:45 PM
  5. speed up vba code within excel macro
    By MOSHEBS in forum Excel General
    Replies: 3
    Last Post: 09-25-2011, 08:23 AM
  6. how to speed up macro code execution?
    By sharmanjali87 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2010, 06:45 AM
  7. Speed Up Macro by Removing Select Commands
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2007, 04:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1