Copy pasting formulas to values too slow

loco2k5

New Member
Joined
Jan 30, 2014
Messages
2
Hi all,

I am facing a problem when i try to simply copy paste formulas into values (for start i do this manually). In detail:

I have a series of formulas in a vertical range (let's say 30.000 rows, from B1 to B30000), that each contains a countifs formula of the following type:

=COUNTIFS(A$1:Axxxx;"Condition") where row is xxxx

Ofcourse it takes some time to calculate (approx 20s), but the problem is when i try to copy/paste it as values, where the time needed is usually more than 6-7 minutes (!).

Also, if i try to do the same in column C (with the same kind of formula, first copy/paste formulas and then copy/paste as values), it requires even more time (so even more than 7 mins for each paste).

I tried to run the formula in safe mode, and i also tried a simple loop macro with copy-pasting cell by cell (looping with an integer from rows 1 to 30000), but it even takes more time.

Any ideas? Thanks to all!

P.S. Using Excel 2007 SP2 with Win7 Ultimate 32b SP1 on an i5 @2.40GHz with 4GB RAM.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
What happens if you use this macro:

Code:
Application.Calculation = xlCalculationManual
With [B1:B30000]
   .Value = .Value
End With
Application.Calculation = xlCalculationAutomatic
 

loco2k5

New Member
Joined
Jan 30, 2014
Messages
2
Hi, thanks for the try.

Unfortunately, same result - the action again takes several minutes to complete, only change that the green bar loading says "Filling Cells" instead of "Paste Cells".

I have tried also with copy-pasting the range as values from a different book, or even putting the formula to run in a different book, and then use external links (from the original to the newly created book) and break them.

Still the results are the same...
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can you reproduce the behavior on other computers?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

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
Top