Efficiency in copying/pasting multiple columns of formulas into values

jaspalsd

Board Regular
Joined
Feb 3, 2014
Messages
72
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi there. Have you tried turning calculation to manual, do the copy/paste values, then turn auto calculation back on.
 
Upvote 0
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
 
Upvote 0
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:
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top