Copy pasting formulas to values too slow

[loco2k5]

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.

 

[wigi]

What happens if you use this macro:

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

 

[loco2k5]

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]

Can you reproduce the behavior on other computers?