+ Reply to Thread
Results 1 to 11 of 11

Is there any way to speed up this macro?

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Is there any way to speed up this macro?

    I wrote the following macro, and although it gets the job done, I notice that it runs very slowly. I'm guessing that has something to do with the way I've organized it...perhaps the nesting?

    Could someone suggest ways of reorganizing this to make it faster?

    For context: I have a large range of data in Columns A through L (data in L, labels in preceding columns), and a list of percentages that accompanies that data. I've placed the percentages off to the side in Columns O and P (O contains labels, P contains the actual percentages), and I need to multiply that range of data by each percentage. To do this, I am copying the original sheet and multiplying it by the percentage. This is repeated multiple times until the complete list of percentages has been completed.

    Slow Macro - Example -Revised.xlsx

    Please Login or Register  to view this content.
    Last edited by kestefon; 11-06-2013 at 01:11 PM.

  2. #2
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is there any way to speed up this macro?

    Added attachment.

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Is there any way to speed up this macro?

    How did you manage to multiply this line?

    Please Login or Register  to view this content.
    Except for column P, the rest of columns do not appear to be numbers.

  4. #4
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is there any way to speed up this macro?

    Oops, that column "L" contains numbers. I've revised the attachment.

    Also, I did some research and I'm wondering if an array would be a more efficient way to handle those percentages. Any thoughts?
    Last edited by kestefon; 11-06-2013 at 01:40 PM.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Is there any way to speed up this macro?

    I have changed your code in to formula. Try module 2 code.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is there any way to speed up this macro?

    Awesome, thank you!

    So, it looks like you just added ".Formula"...any idea why that makes such a difference in speed? (Just trying to get a conceptual understanding.)

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Is there any way to speed up this macro?

    No, that is your code. I was trying to point to you that line could be converted in a formula, which avoids the loop. So, if you look at my code, it fills in the cells with formula and convert them in to values.

    Your code has loop while my does not.

  8. #8
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is there any way to speed up this macro?

    Oh, I see. But don't I still need looping to do all of the subsequent percentages?

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Is there any way to speed up this macro?

    I did not see any other lines of the code. Where are they?

  10. #10
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Is there any way to speed up this macro?

    So, this part of the original code is stepping through each item in columns O:P...

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Is there any way to speed up this macro?

    I thought your tried the code and worked for you. Try it again the code in module 2.
    Attached Files Attached Files

+ 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. vba to speed up macro
    By dulitul in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 09-25-2013, 09:17 AM
  2. Speed up a Macro
    By bungslea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2013, 03:23 AM
  3. Speed-up Macro
    By yunesm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2011, 04:04 PM
  4. Speed Up Macro
    By Pindacko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2007, 12:06 PM
  5. How to speed up this macro?
    By Ctech in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 10-06-2005, 12:05 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