+ Reply to Thread
Results 1 to 3 of 3

More efficient loops or alternatives

  1. #1
    Registered User
    Join Date
    10-24-2019
    Location
    albuquerue
    MS-Off Ver
    2019
    Posts
    2

    More efficient loops or alternatives

    I'm using Excel for a large project and am looking to get the loops to be quicker and more efficient. I am by no means an expert, but I've run in to roadblocks where my code is taking minutes to execute because of some inefficient loops (or vba/formulas in general). While not all my examples I am outlining are loops, I do want to hone in on my skill. I apologize in advanced if I'm terrible at explaining.

    1. This one takes 30 or more seconds and it probably one of the most memory intensive iterations I have in my file with 3 loops total. Essentially I have a few things going on. I'm inserting formulas into 75 rows looping between sheet names C1_Team1 to C7_Team7 . C1_Team1 would be on the first 75 rows, then C2_Team1 would be on rows 75-150 and so on.
    Please Login or Register  to view this content.

    2. This takes about a minute. So I had to split this one up into two parts, the firt loop rebuilds C1_Teams2-4, based off of C1_Team1 as the template. The seconds set of code rebuilds C2_Team1 to C7_Team4 based off of C1_Team1. I couldn't for the life of me figure out how to combine the two sets of code efficiently without screwing up or deleting C1_Team1 which is the template all of the other sheets are built off of
    Please Login or Register  to view this content.
    3. This is probably my fasted loop in the ones I'm trying to make more efficient. I've tried creative ways of making this loop occur quicker, across C, E, & F:I, but I couldn't figure out how to efficiently do a ascii (column) loop. The code is quite quick, but inefficiently written:
    Please Login or Register  to view this content.
    4. This one I couldn't even figure out because I need on Range("D85") to Range("I85") & then Range("M85"), in addition I needed Categories!$A$1 to Categories!$D$1, but just couldn't figure it out. Filldown seemed to be the quickest way to replicate, however these do run quite fast:
    Please Login or Register  to view this content.

    Thank you and all help would be appreciated
    Last edited by ahrion; 04-01-2021 at 12:03 AM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,903

    Re: More efficient loops or alternatives

    Without seeing the workbook, it's hard to say...

    1. Formulas will slow you down. You're writing over 20K of them. Could you not use Autofilter/copy or AdvancedFilter instead of the formulas?
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  3. #3
    Registered User
    Join Date
    10-24-2019
    Location
    albuquerue
    MS-Off Ver
    2019
    Posts
    2

    Re: More efficient loops or alternatives

    Quote Originally Posted by dangelor View Post
    Without seeing the workbook, it's hard to say...

    1. Formulas will slow you down. You're writing over 20K of them. Could you not use Autofilter/copy or AdvancedFilter instead of the formulas?
    The way it is designed, that isn't a possibility because it's built off a "Live" tracker that is constantly changing all day, typically from Office365 Online. So macros are only useful when I'm building/rebuilding or fixing the data people may delete, which is why 1 - 4 are designed. If there was a way to lock worksheets to where ONLY data can be inserted (including data validation, etc), then I would do it that way and most of this code would not be required. (2 would be the only one that would be required because there are 50+ sheets being built off of C1_Team1).

    For 1, If I could reduce 112k+ calculations to 4200 total, do you think this would be helpful or useful?

    The reason C1_Team1 is a template is because it's easier to edit 1 sheet and then have dynamic arrays or other VBA calculations to differentiate the sheets, I don't have to manually edit 50 sheets any time a change is made
    Last edited by ahrion; 04-01-2021 at 03:28 PM.

+ 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. Loops within loops looping
    By Memphismark in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2017, 12:21 PM
  2. How to Change This Code Into Efficient Loops
    By AstToTheRegionalMGR in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2016, 07:00 PM
  3. SUMPRODUCT alternatives
    By KillerGer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-18-2014, 01:31 PM
  4. array alternatives
    By martindwilson in forum The Water Cooler
    Replies: 9
    Last Post: 08-21-2013, 05:59 AM
  5. vlookup issues & more efficient alternatives
    By thecircularwriter in forum Excel General
    Replies: 9
    Last Post: 08-12-2009, 03:58 PM
  6. What are the alternatives ???
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 10:39 AM

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