+ Reply to Thread
Results 1 to 7 of 7

Make a macro faster by calculating some columns only in the end

  1. #1
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Make a macro faster by calculating some columns only in the end

    Hi. I would like to make this macro faster.
    Please Login or Register  to view this content.
    I cannot add the ActiveSheet.Calculate after the Next i because I want to recalculate each time the sheet. However, I have 4 columns (J to M) which I don't want to recalculate each time. Is there anyway it recalculates these columns only at the end of the macro? I'm attaching my macro.

    Also, column A has "opening", "closing" in alternate rows up to A7000. If I add this formula
    Please Login or Register  to view this content.
    will it make the macro faster? (column B could have data up to 7000)

    Thanks for any help!
    RSX P_L ROV test.xlsm

  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: Make a macro faster by calculating some columns only in the end

    Personally I wouldn't use a looping macro, certainly not as a first choice when there are often more elegant and better ways. IMO loops are always a brutal last ditch resort when all else fails.

    In your case the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    put into a spare column on row 6 and copied down will produce the results you want. Then it's a simple matter to copy the column and paste it as values to G6.

    You would also speed up your macro if you avoid .Select and .Activate. These always slow the macro down. Just refer directly to the object in question. For instance your instruction
    Please Login or Register  to view this content.
    is redundant. The line
    Please Login or Register  to view this content.
    is sufficient if the macro is run when the P_L sheet is the active sheet and if its not just qualify the range by adding the sheet name.
    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
    Forum Contributor
    Join Date
    04-15-2015
    Location
    beirut
    MS-Off Ver
    2010
    Posts
    107

    Re: Make a macro faster by calculating some columns only in the end

    Quote Originally Posted by tsakta13ole View Post
    Hi. I would like to make this macro faster.
    Please Login or Register  to view this content.
    I cannot add the ActiveSheet.Calculate after the Next i because I want to recalculate each time the sheet. However, I have 4 columns (J to M) which I don't want to recalculate each time. Is there anyway it recalculates these columns only at the end of the macro? I'm attaching my macro.

    Also, column A has "opening", "closing" in alternate rows up to A7000. If I add this formula
    Please Login or Register  to view this content.
    will it make the macro faster? (column B could have data up to 7000)

    Thanks for any help!
    Attachment 423343
    try this macro
    Please Login or Register  to view this content.

  4. #4
    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: Make a macro faster by calculating some columns only in the end

    @Salim,

    Please avoid posting the whole of the original post it's not necessary and avoids clutter. If necessary and for clarification only you may quote particular sentences or particular questions but only if vital. Most of the time it's not necessary.

  5. #5
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Make a macro faster by calculating some columns only in the end

    @Richard,

    Thank you for your answer. I have this macro because when X6 is copied to G6, G6 changes the value of X7,8 etc. So then I want the new value of X8 to be copied to G8. So, your formula with OFFSET doesn't help me with this problem. That's why I want them to be copied one by one.
    I deleted this
    Please Login or Register  to view this content.
    but my macro takes virtually the same time.

    Also, generally if I add a formula like this in a a column
    Please Login or Register  to view this content.
    instead of opening, closing in alternate rows will it make the macro faster, you think?

    @Salim,

    Thank you for your answer but your macro just puts 100 to columns G and X.

  6. #6
    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: Make a macro faster by calculating some columns only in the end

    To be fair to Salim it's your example macro that adds 100 to column G.

  7. #7
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Make a macro faster by calculating some columns only in the end

    FWIW my example adds 100 to G but then copy/pastes X to G!

+ 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. [SOLVED] Possible to make Macro run faster?
    By wonderd in forum Excel Programming / VBA / Macros
    Replies: 72
    Last Post: 10-08-2014, 04:14 AM
  2. Make Macro run faster
    By pixel34 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2014, 08:54 PM
  3. Make Macro Run Faster
    By quepes13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2014, 02:23 PM
  4. How to make this macro run faster ?
    By vertigo00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2012, 02:01 PM
  5. [SOLVED] How to make the macro run faster?
    By ahng in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-19-2012, 11:48 AM
  6. Can I Make My Macro Faster?
    By PIPPIBOOKS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2008, 06:20 PM

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