How to Create an Array From a Specific Range in Excel Using VBA

Load a cell range into a VBA Variant by assigning the range’s Value property. A range with multiple rows and columns returns a two-dimensional array, so use row and column indexes to read its elements. For a single column or row, Application.Transpose can convert that result to a one-dimensional array.

Arrays in VBA

A VBA array stores an ordered set of values accessed by index. A fixed-size array declares its bounds when it is declared, while a dynamic array can be sized later with ReDim. A Variant is different: it is a flexible variable type that can hold values of different kinds, including an array returned by Excel. Declaring Dim values As Variant does not itself declare a dynamic array.

A multi-cell range assignment is useful when the values will be processed in VBA rather than read one cell at a time. Keep the resulting value in a Variant, because Range.Value returns an array for a multi-cell range.

Load a Specific Cell Range into an Array

Assign the Value property of the range you need. For example, the VBA statement Dim values As Variant: values = Range("A1:B3").Value loads cells A1 through B3. The result has two dimensions: the first index selects a worksheet row and the second selects a column. In this example, the array’s bounds are typically rows 1 through 3 and columns 1 through 2; use LBound and UBound when code needs to determine bounds instead of assuming them.

Read an element with both indexes, as in values(2, 1). This refers to the value from the second row of the selected range and its first column. These indexes address positions in the returned array, not worksheet row and column numbers in general: if the range starts at a different worksheet cell, the first array element still corresponds to the range’s top-left cell.

Convert a Column Range to a One-Dimensional Array

For a single-column range, apply Application.Transpose to the range value to arrange its values in a one-dimensional result. For example: Dim values As Variant: values = Application.Transpose(Range("A1:A3").Value). The values can then be read with one index, such as values(1) for the first item. Keep Application.Transpose spelled exactly as shown; it is the Excel application member used for this conversion.

A small model of the same column ordering is shown here:

cells = [["Ava"], ["Bo"], ["Cy"]]
values = [row[0] for row in cells]
print(values)

['Ava', 'Bo', 'Cy']

This Python illustration shows the shape change only; it does not run Excel or execute VBA. Use the VBA statement above in an Excel macro.

Convert a Row Range to a One-Dimensional Array

A range containing one row starts as a two-dimensional result too. For a one-dimensional array, apply Application.Transpose twice to the range value: Dim values As Variant: values = Application.Transpose(Application.Transpose(Range("A1:C1").Value)). The resulting items can be read with one index, for example values(1) for the value from A1.

The column and row patterns are separate because the orientation of the selected range affects the intermediate result. Use the single-transpose form for a column and the nested form for a row. If your procedure needs to support ranges of different sizes, determine the resulting bounds with LBound and UBound before iterating. These functions report the lower and upper index values. Calculate an array’s element count as UBound(array) - LBound(array) + 1; the upper bound alone is not the count, including when the lower bound is zero.