Union of Ranges out of order

Question

I am trying to copy various ranges in a certain order, and than paste them from a workbook into a different workbook.

Currently

I have set my ranges eg

Set rg = ws1.Range("A2:A" & i).Offset(rowOffset:=1, columnOffset:=0)
Set rg1 = ws1.Range("Z2:Z" & i).Offset(rowOffset:=1, columnOffset:=0)
Set rg2 = ws1.Range("C2:C" & i).Offset(rowOffset:=1, columnOffset:=0)

Set TradesCopy = Union(rg, rg1, rg2)

So typically what should happen is that it should be pasting in those ranges in that order (rg, rg1, rg2),

however this is being pasted rg, rg2, rg1

Can anyone tell me what I can do to fix this problem?

Answer 1

If you build a 2-D array of the values from the worksheet in the order you want them, you can put them in any order and it will be faster than multiple Copy & Paste operations.

Sub wqewqteq()
    Dim a As Long, b As Long, c As Long, lr As Long
    Dim vCOLs As Variant, vVALs As Variant, vSRCs As Variant

    vCOLs = Array(1, 26, 3)     'could also be Array("A", "Z", "C")

    With Worksheets("Sheet8")
        lr = Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, _
                             .Cells(.Rows.Count, "C").End(xlUp).Row, _
                             .Cells(.Rows.Count, "Z").End(xlUp).Row)
        vSRCs = .Range(.Cells(2, "A"), .Cells(lr, "Z")).Value2
        ReDim vVALs(1 To lr - 1, 1 To UBound(vCOLs) + 1)

        For a = 1 To lr - 1
            For b = LBound(vCOLs) To UBound(vCOLs)
                vVALs(a, b + 1) = vSRCs(a, vCOLs(b))
            Next b
        Next a

        .Cells(2, "AB").Resize(UBound(vVALs, 1), UBound(vVALs, 2)) = vVALs

    End With

End Sub

I could find no mention of a specific destination so I used AB2 on the same worksheet.

Remember that you are dealing with both 1-D and 2-D arrays here. 1-D arrays default with a lower boundary of zero (lbound(vCOLs) == 0); 2-D arrays default with a lower boundary of one for each rank (lbound(vVALs, 1) == lbound(vVALs, 2) == 1).