Problem with speed of PasteSpecial for large ranges

[xlb]

Hello,

With Excel 2002, I have a VBA macro :

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With MyRange
. Formula = .Value
End With

I am very surprised by the non proportionnal augmentation of the time with
the size of the range
Example of augmentation in width
1000 x 50 : 2"
1000 x 100 : 9"
1000 x 200 : 40"

Example of augmentation in height
1000 x 20 : 1"
2000 x 20 : 13"
3000 x 20 : 29"
4000 x 20 : 45"

Example for 100.000 cells :
1000 x 100 : 9"
2000 x 50 : 28"
4000 x 25 : 57"
10000 x 10 : 79"

Is it a known problem ?
Is there a solution : other method for pasting ? sclicing in several zones ?

Xavier

 

[Tonhao]

I have the SAME problem since (old days) Excel'95
The solution is "splice" the range in many pieces balancing the range for
"Number of Pieces x Performance"

 

[Dave Peterson]

You didn't actually copy|paste special|values, you just assigned the values.

You may want to test copy|paste special|values to see if it's any quicker (I
didn't test it):

with myRange
.copy
.pastespecial paste:=xlpastevalues
end with

 

[xlb]

Thank you for your response

I have tested both. The results are the same : the copy paste values is
correct and the speed is the same.

It is extremely strange. This morning, the 2000 x 50 was very fast 0.2" !
I quit Excel, do something else, try another test : 11" !
(My mesures are done with GetTickCount)

Any idea ?

Xavier

 

[Dave Peterson]

No really.

Get a faster pc???

Thank you for your response

I have tested both. The results are the same : the copy paste values is
correct and the speed is the same.

It is extremely strange. This morning, the 2000 x 50 was very fast 0.2" !
I quit Excel, do something else, try another test : 11" !
(My mesures are done with GetTickCount)

Any idea ?

Xavier