VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index

Sometimes we need to slice an array i.e. fetch a row/column from a multidimensional array. There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. However it can be done using a worksheet function named Index.

Syntax :

  Application.Index(Array, Row_Number, Column_Number)
OR
  Application.WorksheetFunction.Index(Array, Row_Number, Column_Number)

To extract a column from the source array, ‘0’ should be passed as row_number argument. Similarly, to extract a row from source array, ‘0’ should be passed as column_number argument.

e.g.

Sub Test()

    Dim varArray            As Variant
    Dim varTemp             As Variant

    varArray = ThisWorkbook.Worksheets("Sheet1").Range("A1:E10")
    varTemp = Application.Index(varArray, 2, 0)

End Sub

In the above example second row of varArray will be fetched in varTemp. We can also extract more then one row/column at the same time. In this case an array of numbers (indicating row/column) should be passed as row_number / column_number argument.

e.g. If in the above example we need to extract 2nd, 4th and 5th rows then row number would be passed as an array:

varTemp = Application.Index(varArray, Array(2, 4, 5), 0)

Similarly, to extract columns:

varTemp = Application.Index(varArray, 0, Application.Transpose(Array(2, 4, 5)))

This function can also be used to fill values in a particular row/column of a range.

Syntax : Application.Index(Range, Row_number , Column_number) = SourceArray/Range

In the same example to fill the values of 2nd column of varArray to 2nd column of range [A1:E10], we would do the following

Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)