范围联盟混乱 [英] Union of Ranges out of order

问题描述

我试图按一定顺序复制各种范围，而不是将它们从工作簿粘贴到其他工作簿中。

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

当前

我已经设置了范围，例如

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)

因此通常应该发生的是按该顺序粘贴在这些范围内（rg ，rg1，rg2），

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

但是要粘贴的内容rg，rg2，rg1

however this is being pasted rg, rg2, rg1

有人可以告诉我我该怎么做才能解决此问题？

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

推荐答案

如果您从工作表中的值构建二维数组可以按任意顺序排列它们，这将比多次复制和打印速度更快。粘贴操作。

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

我找不到任何特定目的地，因此我在同一工作表上使用了AB2。

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

请记住，您在这里处理一维和二维数组。一维数组的默认下限为零（lbound（vCOLs）== 0）;二维数组默认为每个等级的下边界为1（lbound（vVALs，1）== lbound（vVALs，2）== 1）。

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).

这篇关于范围联盟混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！