Big excel file with vba, slow processing

[lukeckh]

Hi guys,

I've got a problem. I have an excel file with around 200,000+ rows with around 30 columns. This file contains functions such as IF, AND, OR as well as certain functions that I have written as there are certain cells that required my own formulas. Without any automation, the file size is around 12 mb.

I am trying to automate the entire process and now I have gotten up to around the 25th column and the process is starting to look really slow and bleak as the file size has increased to around 40mb.

Is there anyway by which I can make make the code more efficient? The current code I am using is:

Please Login or Register  to view this content.

----

I am pretty much a noob in VBA, but as you can see everytime I run this code, the GCDnm formula will be pasted all through the J column. This process is repeated for all the rows and different columns have different code. Is there a way by which I can do it in a more efficient way? i.e. everytime a new line is added onto the 200,000++ lines of data the code doesn't have to paste the formula for the entire column??

Sorry for the long post! Many thanks!

[abousetta]

Hello and Welcome to the Forum,

Whenever you are putting formula via vba, we should (usually) turn off calculations until the macro is done so that it doesn't keep starting to calculate over and over and over again each time you add a new formula (which sometimes happens).

Try this:

Please Login or Register  to view this content.

abousetta

[abousetta]

Hi,

Thanks for your question regarding ScreenUpdating. I didn't want to just throw information at you so I didn't elaborate at why I turned it off at the beginning of the code, but didn't turn it on again at the end. Simply put, Excel will do this regardless whether or not I tell it to once the macro reaches 'End Sub'. Therefore there's no real reason to tell Excel to do if it's going to do it anyways. As for the others, if you don't tell them to revert to the original state, then they will remain turned off.

Hope this helps.

abousetta