无法对阵列进行切片

行数组必须是垂直的：

arr = Application.Index(Sheet2.Range("A:F"), Application.Transpose(Array(100, 500, 900)), Array(1, 2, 3, 4, 5, 6))

人们总是可以创建一个从 1 到列数的一维数字数组，并使用它：

Dim rng As Range
Set rng = Sheet2.Range("A:F")

Dim test As Variant
ReDim test(rng.Columns.Count - 1)

Dim i As Long
For i = LBound(test) To UBound(test)
    test(i) = i + 1
Next i

Dim arr As Variant
arr = Application.Index(rng, Application.Transpose(Array(100, 500, 900)), test)

切片数组行

• 请注意，对于连续列，您可以使用：

  [Row(1:1)] ' first column
  [Row(1:3)] ' first three columns
  [Row(2:5)] ' four columns after the first
  [Row(3:4)] ' two columns after the second
  

• 如果您还需要灵活地选择不连续的列，那么您可以使用以下方法代替：[Row(1:6)]

  Application.Transpose([{1,3,4,6}])
  Application.Transpose(Array(1, 3, 4, 6))
  

快速修复

• 从 2D 1 个数组中的 2D 1 个数组返回 3 行乘以 6 列。

Sub QuickFix()
    ' Don't use entire columns, it takes too long.
    Dim rg As Range: Set rg = Sheet2.Range("A1").CurrentRegion.Columns("A:F")
    'Debug.Print rg.Address(0, 0)
    Dim arr As Variant
    arr = Application.Transpose(Application.Index(rg.Value, [{100,500,900}], [Row(1:6)]))
    ' Three rows, six columns ('H1:M3')
    Sheet2.Range("H1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

一项研究

Sub Random()
    Dim arr() As Variant
    
    ' A Row (1D one-based: one row, three columns)
    arr = [{3,5,7}]
    Debug.Print "Row (Random)"
    Debug.Print LBound(arr), UBound(arr)
    
    ' A Column (2D one-based: three rows, one column)
    arr = Application.Transpose([{3,5,7}])
    Debug.Print "Column (Random)"
    Debug.Print LBound(arr, 1), UBound(arr, 1), LBound(arr, 2), UBound(arr, 2)

End Sub

Sub Sequence()
    Dim arr() As Variant
    
    ' A Row (1D one-based: one row, six columns)
    arr = Application.Transpose([(Row(1:6))])
    Debug.Print "Column (Sequence)"
    Debug.Print LBound(arr, 1), UBound(arr, 1)

    ' A Column (2D one-based: six rows, one column)
    arr = [Row(1:6)]
    Debug.Print "Row (Sequence)"
    Debug.Print LBound(arr, 1), UBound(arr), LBound(arr, 2), UBound(arr, 2)

End Sub

Sub TwoD()
    
    ' Source Array (2D one-based: ten rows, six columns)
    Dim sData As Variant: sData = Range("A1:F10")
    Debug.Print "Source"
    Debug.Print LBound(sData, 1), UBound(sData, 1), LBound(sData, 2), UBound(sData, 2)
    
    ' Transposed Array (2D one-based: six rows, three columns)
    Dim tData As Variant
    tData = Application.Index(sData, [{3,5,7}], [Row(1:6)])
    Debug.Print "Transposed (Wrong)"
    Debug.Print LBound(tData, 1), UBound(tData, 1), LBound(tData, 2), UBound(tData, 2)
    
    ' Destination Array (2D one-based: three row, six columns)
    Dim dData As Variant
    dData = Application.Transpose(Application.Index(sData, [{3,5,7}], [Row(1:6)]))
    Debug.Print "Destination (Correct)"
    Debug.Print LBound(dData, 1), UBound(dData, 1), LBound(dData, 2), UBound(dData, 2)

End Sub