Slow running macro

Nice one Kaak,

I didn't consider removing all the range variables but it's probably fair enough considering that they are only referred to once/twice.

Pjjclark, another variation which I made before I saw Kaak's version is below & uses a select case layout rather than If clauses & tries to remove any repetion from w/in the cases. I've put in the "set... = nothing" to try & prevent "stack overflow" which I've never experienced but have read about.

change the order of the Select Case options so that the most likely one is listed first. If this means reversing a case "" with a case else, then you could change the code to read case <> "" and case else.

Please Login or Register  to view this content.

Another option would be to use the above select cases/If layout with Kaak's suggestion of "range(..." or to use the "cells(i,..." as needed.

Let us know how it goes - I'm interested to see which approach is the fastest.

hth
Rob

The obvious other things to try are turning off the calculation and screenupdating at the top of the macro and turning it back on at the end, are there other formulas on the worksheet that may be recalculating every time you update the sheet?

Please Login or Register  to view this content.

any quicker?

Regards

Dav

ahhhh, of course...
Duh me, why didn't I think of suggesting that?
note to self - take my blinkers off!
(I usually throw the screen updating one in but forgot this time & I never even thought of the calc' mode.)

You may have to be cautious using the "Application.Calculation = xlCalculationManual" in some of your other macros ie if the macro uses calculated values which are collected from the spreadsheet during runtime it may pick up an incorrect value.

Rob

This link is a good read. Gives various tips including clear some old junk off the PC to speed it up

http://www.decisionmodels.com/optspeedd.htm

VBA Noob