Unable to slice an array-CodePudding

I'm trying to get data from non-contiguous rows. The columns are fixed but the row numbers vary. The result I'm expecting is a 2-D array. I don't know what I'm doing wrong but the slicing using index function is not working.

'Just for example to get the data from row number 100, 500 and 900 and the columns from A to F
arr = Application.Index(Sheet2.Range("A:F"), array(100, 500, 900))

I thought if 3rd parameter is left blank row slicing will be done. But the resultant array is single dimensional (size 3) and is populated with Error 2023.

So I gave another try by giving 3rd parameter too.

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

Even now the resultant array is single dimensional (size 6) but first three indices have data and the rest have Error 2042. Is it possible at all to get a 2-D array by slicing a 2-D array? If yes, please point me in the right direction.

CodePudding user response：

The row array needs to be vertical:

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

One can always create a 1D array of numbers from 1 to the number of columns and use that:

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)

CodePudding user response：

Slice Array Rows

Note that for contiguous columns you can e.g. use:

[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

If you need the flexibility of also choosing non-contiguous columns, then instead of [Row(1:6)] you could e.g. use:
```
Application.Transpose([{1,3,4,6}])
Application.Transpose(Array(1, 3, 4, 6))
```

A Quick Fix

Returns three rows by six columns from a 2D one-based array in a 2D one-based array.

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

A Study

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