CODEWITHSUNDEEP
excelvba
Untitled
Untitled

Reputation: 63

How to speed up Copy Paste Values when using Offset

I want make this basic function of "copy&paste-values-on-a-new-row-each-time" run as fast as possible since the macro repeats the calculations hundreds of thousands of times. I just can't find the exact answer after searching this forum for ages.

Currently, I'm copying output numbers from a fixed range and, elsewhere on the worksheet, pasting the values on a new row for each new set of results.

Here's the portion of the code doing this:

Row = Row +1
Range("g15:ax15").copy
Range("ea18").select
ActiveCell.Offset(Row,0).select
Selection.PasteSpecial Paste:=xlPasteValues

Now from what I have found on this forum, I can replace the Copy/Paste functions completely with Range(destination).value = Range(results).value to speed things up. However, I can't figure out how to do this if the destination rows need to be offset by 1 each time. Also, I've read that one could even do away with "select" to speed things up further! How?

Upvotes: 1

Views: 7917

Answers (3)

brettdj
brettdj

Reputation: 55682

You can do it without copying as yo mention (using a variant array as you are copying values only, not formats)

X = Range("g15:ax15").Value2
[ea18].Offset(1, 0).Resize(UBound(X, 1), UBound(X, 2)).Value2 = X

or with your variable offset

Dim lngCnt As Long
lngCnt = lngCnt + 1
X = Range("g15:ax15").Value2
[ea18].Offset(lngCnt, 0).Resize(UBound(X, 1), UBound(X, 2)).Value2 = X

Upvotes: 1

Alex P
Alex P

Reputation: 12487

There are a number of options:

//This uses the `Destination` key word
Sub CopyAndPaste()
    Dim i as long
    For i = 1 to 10
        Range("g15:ax15").Copy Destination:=Range("ea18").Offset(i, 0)
    next i
End Sub

//If you need `PasteSpecial` then you cannot use `Destination` hence this version
Sub CopyAndPaste()
    Dim i as long
    For i = 1 to 10
        Range("g15:ax15").Copy
        Range("ea18").Offset(i, 0).PasteSpecial Paste:=xlPasteValues
    next i
End Sub

Sometimes reading values into an array first and then writing back to the spreadsheet is quicker. Here is an example:

Sub CopyAndPaste()
    Dim i As Long, numbers As Variant, rw As Long

    numbers = Range("g15:ax15")
    rw = 18

    For i = 1 To 10
        rw = rw + 1
        Range(Cells(rw, 131), Cells(rw, 131 + UBound(numbers, 2) - 1)) = numbers
    Next i    
 End Sub

Upvotes: 1

user3819867
user3819867

Reputation: 1120

Row = Row +1
 Range("g15:ax15").copy
 Range("ea18").Offset(Row,0).PasteSpecial Paste:=xlPasteValues

Select is a more-or-less useless method inherited from recordings.

Upvotes: 0

Related Questions