Speeding/Optimizing looping macro help

  • I have attached some sample data that gets updated daily and ends up being 70,000 plus rows after completion of the below code.


    The macro below essentially inserts rows for missing dates and copies values down to those rows. After all the rows are inserted the macro will then insert formulas into columns so that calculate max/min and week number for displaying later. This data is refreshed via SQL call every day and the code is then run to complete the missing data.


    Currently this macro takes [SIZE=16px]40 min [/SIZE]to run and I have exhausted all of my knowledge on how to speed it up. I have read that it may be faster to use arrays and or variant to perform looping inserts like below but I just don't have any experience with this method and cant figure it out. Lastly I know there are some "Volatile" functions that should be avoided but I am not sure exactly how many I am using and how to avoid them. At this point I would be open to any ideas on how to speed this up.


    Thank you all for any ideas :idea:


  • Sorry - this isn't likely to help you much, but I have had a look and can't see anything obvious that I would do differently....


    That said, when I run your FillDates macro on your attached file on my machine it takes something less than 30 seconds to complete... This is on an Intel Core i7 based laptop with 8Gb RAM running 64-bit Office 365... Perhaps a hardware upgrade is the solution?

  • Thank you Infomage,
    Our work machines are 64 bit Windows 10 computers with 8GB of Ram and i5 2.3 GHz processors. When we were using windows 7 we never had these issues, however; when we all switched over to windows 10 every macro we have that loops went to crap and slowed way down. I appreciate your effort and response, sadly I believe the only way my issue will be resolved is like you suggested. Get a faster processor or find a way to get a Windows 7 machine back (will never happen since were not supporting anymore for security reasons).


    Thanks again,