循环通过具有可变边界的不同范围

Question

因此，我试图将不同的列复制到使用的最后一行，并在我试图访问的每个范围中循环。为此，我声明了一个数组，用于存储范围的前半部分(固定(，并将最后一行添加&Range((内的LastRow，但当循环通过这些范围时，我总是收到一个错误运行时错误9下标超出范围(循环的第一行被标记(。我只是不明白为什么这不起作用(将&LastRow存储在数组中也不起作用(。我需要这个LastRow是可变的，因为它将在宏的后面部分更改。

Sub ImportRows()
Dim wbk As Workbook
Dim LastRow As Long
Dim CopiedColumns As Variant
Dim InsertColumns As Variant
Dim i As Integer
Set wbk = ActiveWorkbook
CopiedColumns = Array("A2:A", "C2:C", "F2:F", "J2:J", "L2:L")
'Copy Paste
'###########################################
InsertColumns = Array("A2:A", "B2:B", "C2:C", "D2:D", "E2:E")
With wbk.Worksheets("RawData")
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
For i = 1 To 5
wbk.Worksheets("RawData").Range(CopiedColumns(i) & LastRow).Copy
wbk.Worksheets("Comparrisson").Range(InsertColumns(i) & LastRow).Insert
Next i
End Sub

感谢您的帮助！

Answer 1

正如我在评论中所说，如果迭代适应1D数组类型，那么您的代码应该可以工作。请测试下一个代码，只使用数组，不涉及剪贴板，降低Excel速度并消耗更多资源：

Sub ImportColumns()
Dim wbk As Workbook, LastRow As Long, CopiedColumns, InsertColumns As Variant
Dim sh As Worksheet, sh1 As Worksheet, i As Long, arrC
Set wbk = ActiveWorkbook
CopiedColumns = Array("A2:A", "C2:C") ', "F2:F", "J2:J", "L2:L")
Set sh = wbk.Worksheets("RawData")
Set sh1 =  wbk.Worksheets("Comparrisson")
'Copy columns
'###########################################
InsertColumns = Array("A2", "B2", "C2", "D2", "E2")
LastRow = sh.cells(sh.rows.count, 1).End(xlUp).row
For i = 0 To 1
arrC = sh.Range(CopiedColumns(i) & LastRow)
sh1.Range(InsertColumns(i)).Resize(UBound(arrC), 1).value = arrC
Next i
End Sub

还有一个更紧凑的解决方案，不涉及任何迭代：

Sub ImportColumnsArr()
Dim wbk As Workbook, LastRow As Long, arr, arrC
Dim sh As Worksheet, sh1 As Worksheet
Set wbk = ActiveWorkbook

Set sh = wbk.Worksheets("RawData")
Set sh1 = wbk.Worksheets("Comparrisson")
LastRow = sh.cells(sh.rows.count, 1).End(xlUp).row
arr = sh.Range("A2:L" & LastRow).value

arrC = Application.Index(arr, Evaluate("row(1:" & UBound(arr) & ")"), Array(1, 3, 5, 10, 12))
'Copy columns:
sh1.Range("A2").Resize(UBound(arrC), UBound(arrC, 2)).value = arrC
End Sub