Problem with speed of PasteSpecial for large ranges
 

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

Problem with speed of PasteSpecial for large ranges
 

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"

"xlb" wrote:

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

Problem with speed of PasteSpecial for large ranges
 

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 wrote:

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

--

Dave Peterson

Problem with speed of PasteSpecial for large ranges
 

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" a écrit dans le message de news:
...
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 wrote:

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

--

Dave Peterson

Problem with speed of PasteSpecial for large ranges
 

No really.

Get a faster pc???


xlb wrote:

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" a écrit dans le message de news:
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson