Efficiency in copying/pasting multiple columns of formulas into values

[jaspalsd]

Hi,

I'm have multiple columns of long-winded nested IF statements with a few countif conditions and when I try copying/pasting as values in one attempt it is taking at least 1.5 hours for the process to be complete - this gets longer with each month due to the increased number of records.

From experience, can anyone tell me if it is better to copy/paste columns in parts instead of doing it in one go so that going forward I can look to make the process far more efficient and have less risk of the file crashing and wasting valuable time

Thanks!

 

[jmacleary]

Hi there. Have you tried turning calculation to manual, do the copy/paste values, then turn auto calculation back on.

 

[jaspalsd]

Hi,

Yes the calculations are set to manual when I copy/paste.

My main worksheet will have 20k+ rows and a few columns of calculations where logic is applied mostly using nested if statements with countifs

 

[offthelip]

I think the problem might be because when you do a "copy" excel doesn't know what you are going to paste so it has to copy everything in the range, whic hends up being a lot of data. You can avoid this and only copy the values by using Variant arrays.
Assuming you know the last column and the last row ( which you can work out you can try this code:

Dim inarray as variant
inarray=range(cells(1,1),cells(lastrow,lastcolumn))
range(cells(1,1),cells(lastrow,lastcolumn))=inarray

It should be very fast