Should I be content with the time code takes?

[L. Howard]

This copy/paste special code is the last part of a macro that I run 100 times in a loop.

Using a timer, it takes 5.397 seconds to run. It produces 2000 rows of output data. Not too bad in my eyes.

If I comment out the copy lines, the macro runs 100 times in .847 seconds. (Thanks Claus)

I am not unhappy with the 5 + seconds run time, and the six square wheeled copy wagons the macro has to drag along are necessary.

Was just wondering if the copy paste could be written to be quicker. If not, I am still a happy camper and content with the 5 + seconds.

I pondered an array approach, but the six ranges to six different destinations were beyond me.

Thanks,
Howard

Range("AQ2:AQ21").Copy
Range("AE" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Range("AQ24:AQ43").Copy
Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Range("AQ46:AQ65").Copy
Range("AI" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Range("AQ68:AQ87").Copy
Range("AK" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Range("AQ90:AQ109").Copy
Range("AM" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Range("AQ112:AQ131").Copy
Range("AO" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

 

[Claus Busch]

Hi Howard,

Am Fri, 14 Mar 2014 03:54:49 -0700 (PDT) schrieb L. Howard:

This copy/paste special code is the last part of a macro that I run 100 times in a loop.

Using a timer, it takes 5.397 seconds to run. It produces 2000 rows of output data. Not too bad in my eyes.

try:

Sub MyCopy()
Dim i As Long
Dim arrOut As Variant
Dim myCol As Long

myCol = 31
For i = 2 To 112 Step 22
arrOut = Range("AQ" & i).Resize(rowsize:=20)
Cells(Rows.Count, myCol).End(xlUp).Offset(1, 0) _
.Resize(rowsize:=20) = arrOut
myCol = myCol + 2
Next
End Sub


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Fri, 14 Mar 2014 03:54:49 -0700 (PDT) schrieb L. Howard:






try:



Sub MyCopy()

Dim i As Long

Dim arrOut As Variant

Dim myCol As Long



myCol = 31

For i = 2 To 112 Step 22

arrOut = Range("AQ" & i).Resize(rowsize:=20)

Cells(Rows.Count, myCol).End(xlUp).Offset(1, 0) _

.Resize(rowsize:=20) = arrOut

myCol = myCol + 2

Next

End Sub





Regards

Claus B.

--

Indeed, drops it back to 1.532 seconds.

Mighty fine!

Thanks Claus.

Howard