I have a very slow, time consuming macro (takes approx 25 mins to run on i7 32GB), that throws up the lack of resource Excel error and I was hoping somebody with more knowledge on VBA had any tips to improve the code and make it more efficient?
Part of the code below...
Any help greatly appreciated
Please Login or Register to view this content.
Last edited by Dgp2012; 07-18-2013 at 11:29 AM.
Hi Dgp2012,
Check if the Copy and Paste special values parts can be done on entire range of formula portion at one go.
regards
taps
Untested!
Provided your paste is correct, try this code. Since it is untested, it might throw you an error. If you get an error, tell me which line.
Please Login or Register to view this content.
Sorry for the delayed response...
Thank you for your suggestion, I have applied the code and works great, with a slight amendment.
For example:
It has made it marginally quicker by 1-2 minutes which is great.Please Login or Register to view this content.
I also came across the Application.DisplayAlerts function which is excellent as it skips the resource warning, but I still would like to improve the speed of this code...any ideas ?!
I'd suggest you try restricting the vlookup formulas to not look at entire columns when looking for an exact match
Josie
if at first you don't succeed try doing it the way your wife told you to
For A-1 on 'Sales Data'.
Please Login or Register to view this content.
If posting code please use code tags, see here.
I have just applied this code - incredible ! Took approx. 3 minutes, not 30 !!!Originally Posted by Norie
For A-1 on 'Sales Data'.
Please Login or Register to view this content.
Can i ask, if i wanted to calculate columns in a specific order - could this be done? i.e. calculate column N before M as M is dependent on N? I can work around this if its not possible.
Also, how would i apply this new code to the second part of my initial code on the "Fixed" sheet. The formula was too long for VBA so had to store it in a cell.
Thank you so much for your help!
I'd suggest setting calculation to off whilst running the macro then calculating everything at the end?
Thanks Yudlugar,
I am trying this now to see which method is quicker, straight to values or leave the formula until the end. Doing it this way would also mean i wouldn't have to calculate columns in a specific order too!
I don't think the dependency should matter.
All the formulas will be calculated at the same time as they would if you just copied them down.
What I posted can only be used for contiguous ranges, for example columns A:I.
The rest of your code seems to be kind of jumping all over the place.
Mind you there does seem to be a pattern in the code for 'Fixed'.
If there is then you might be able to use a loop for that part.
I'll have a closer look.
I've kind of checked this, making sure the right columns/rows are being populated, but that's all.
Please Login or Register to view this content.
Oh, forgot to add - if this is running slowly then it might not be the code, have a look at the formulas.
As Joseph has already suggested try not using entire columns with things like VLOOKUP.
Thank you so much for your help - this has REALLY helped me !!
I am going through all my VLOOKUPS and SUMIFS etc., and limiting to the range if its fixed to further improve the file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks