What are the best ways to move excel ranges around using VBA?

Question

What are the best ways to move excel ranges around using VBA? For as frequently as I need to do it, I still have a lot of trouble with it. I'd like to show my most-used approaches for criticism and/or suggestions of new ways to go about moving ranges.

The cut/copy method seems to be really finicky, especially when multiple worksheets or variables are involved. For example:

    Dim wkb1 as Workbook
    Dim wks1 as Worksheet, wks2 as Worksheet

    Set wbk1 = ThisWorkbook
    Set wks1 = wbk1.Worksheets(1)
    Set wks2 = wbk1.Worksheets(2)

    wks1.Range("A1:A5").Copy (wks2.Range("A1"))
        ' Error: Object variable or With Block variable not set.

    wbk1.Worksheets(1).Range("A1:A5").Copy (wbk1.Worksheets(2).Range("A1"))
        ' This works.

I would think that avoiding the clipboard increases execution speed. Here, I simply equate one range to another. The drawback is that it requires ranges of equal dimensions:


' Copy a range of two equal dimensions.
    WrkSht2.Range("A1:F" & (rngEndRange.row - 10)).Value = _
        WrkSht2.Range("A10", rngEndRange).Value

I like the idea of using collections, but there's the draw back of added complexity from needing to work with loops.


Sub UseCollection()

    Dim MySheet As Worksheet
    Dim lngLastRow As Long
    Dim rngSearch As Range, rngCell As Range, rngCopy As Range
    Dim MyCollection As New Collection

    Set MySheet = ThisWorkbook.Worksheets(1)
    Set CopySheet = ThisWorkbook.Worksheets(3)

    lngLastRow = MySheet.Range("A1").End(xlDown).Row
    Set rngSearch = MySheet.Range("A1:A" & lngLastRow)
    Set rngCopy = CopySheet.Range("A1")

    For Each rngCell In rngSearch
        MyCollection.Add rngCell.Value
    Next rngCell

    i = 1
    For Each Element In MyCollection
        rngCopy(i, 1) = Element
        i = i + 1
    Next Element

End Sub

So how do you all go about copying ranges? Ideally, an approach should be easy to use with worksheet/range variables, and it should be relatively simple.

Note: **remove** the parentheses around the destination: `wks1.Range("A1:A5").Copy wks2.Range("A1")`... see [this question](https://stackoverflow.com/questions/58996540/how-does-byref-to-byval-by-parentheses-work-when-the-byref-is-a-range-with-a-wo). I personally find this style clearer: `wks1.Range("A1:A5").Copy Destination:=wks2.Range("A1")`. — BigBen, Nov 22 '19 at 17:18
As far as value transfer `[...].Value = [...].Value` - use `Resize` to get a range of the same size. — BigBen, Nov 22 '19 at 17:22

score 1 · Accepted Answer · answered Nov 22 '19 at 17:24

1

Yes the range size is a slight faff which is one reason why I often don't bother, but you can do it this way:

Sub x()

Dim r1 As Range

Set r1 = Range("A1:C3")

With r1
    Range("F1").Resize(.Rows.Count, .Columns.Count).Value = r1.Value
End With

End Sub

Edit: think this is what BigBen's comment means.

answered Nov 22 '19 at 17:24

SJR

22,986
6
18
26

2

They really need to reduce that minimum character limit. – SJR Nov 22 '19 at 17:32
1

Thanks! Works nicely. – LabRat01010 Nov 22 '19 at 18:17
2

My pleasure. You might want to get in the habit of accepting answers to your questions. – SJR Nov 22 '19 at 18:28

score 0 · Answer 2 · answered Nov 22 '19 at 18:43

0

...Or a bit shorter

Sub x()

With Range("A1:C3")
    Range("F1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

End Sub

answered Nov 22 '19 at 18:43

barneyos

586
2
5
7

What are the best ways to move excel ranges around using VBA?

2 Answers2