VBA - Macro runs v slow!! Takes 8 minutes to complete !

[jonny1984]

Hi

I have a macro that I am running which takes a very long time to run - I even built a Timer function to calc and on average it takes about 8 minutes to complete!!

I have tried a lot of the usual "tricks" - e.g. Calculation = Manual, SceenUpdating = False, EnableEvents = False

But these don't seem to speed it up much

LOT OF DATA : It may just be a case there is a lot of data and this is how long it will take to process. The sheet has circa 350,000-400,000 rows. And about 25-30 columns. Pretty much all with formulas in .. so perhaps its just simply the amount of data I am trying to process.

With all the formulas I wrote some VBA to enter the code into all the cells . But the formula only technically needs to go in once in the top row and then just be copied down as the formula remains the "same" (adjusting automatically for the row number)


For example .... My Code -:

' Enter formula in column J and copy down to last row (lreur)
Sheets("Data").Range("J4:J" & lreur).FormulaR1C1 = _
"=IF(RC[-2]=""Long"",RC[-1]*(1-Master!R5C3),IF(RC[-2]=""Short"",RC[-1]*(1+Master!R5C3)))"


Basically it could enter that formula into just Cell J4 .. and then copy it down through all the rows.. would that be quicker ?

Is there anything else obvious with the above formula that might slow it down ?

Waiting 8 mins to run the macro every time makes my work very slow and tedious !!!


Thanks in advance for any help !

Jonny

 

[mse330]

Hey Jonny,

It looks like your data is big as explained & you've done the usual tricks already. I think if you post the full code that you are usuing, perhaps members here will be able to assist more as most of us won't be able to suggest anything blindly. One more trick that "might" help a little is to save your workbook in .xlsb format

 

[John_w]

Cross-posted - please post links to your posts on other forums.

 

[Derek Brown]

Are you using any volatile functions?
Have a look at the following:

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