How to Convert Range to Array in Excel VBA (3 Ways)

Below is a dataset with the names of students and their marks in Physics, Chemistry, and Mathematics at Sunflower Kindergarten.

Method 1 – Convert a Range to a Two-Dimensional Array Using the Range Object of Excel VBA

Steps:

Here we’ll convert the range B4:E13 into an array of dimensions 10, 4 (Row 10, Column 4).

• Declare the name of the array using the data type Variant. Here, we’ve declared it as Myarray.

• Assign your desired range (B4:E13 in this example) using the Range property of VBA.

• The complete VBA code will be:

⧭ VBA Code:

Sub Convert_Range_to_Two_Dimensional_Array()

Dim Myarray As Variant

Myarray = Range("B4:E13")

End Sub

Note: This code creates a Macro called Convert_Range_to_Two_Dimensional_Array.

Code Breakdown

The code converts the range B4:E13 of your active worksheet into an array of row 10 and column 2.

Now if you want to see any specific value of the array, just put one line of code at the end mentioning the indices of the value in a message box.

For example, to see the marks in Chemistry of the 5th student, put:

• Run the code. You’ll find a message box displaying 57, the marks of the 5th student in Chemistry.

•  Instead of fixing the range in the code, you can use an Input Box to ask the user to enter the range each time he/she runs the Macro.

This will add a bit more flexibility to the code.

Enter the following code:

• Run the code. It will first ask for the range.
• Enter the range and click on OK. Here I’ve entered B4:E13.

• It’ll convert the range to an array.

This code only works for the range of the active worksheet. To make it a bit more rigid, you can fix the worksheet’s name before the Range object.

For example, to convert the range B4:E13 of the worksheet named Sheet1 to an array, you can enter:

It’ll convert the range B4:E13 of Sheet1 to an array, no matter what the active worksheet is.

Method 2 – Transfer a Range to a One-Dimensional Array Using the Transpose Property of Excel VBA

You have to use the Transpose property of VBA to convert a range to a one-dimensional array.

Converting a Single Column

Steps:

• Wrap the column within the Transpose property of VBA once.
• To convert the array B4:B13 (Student Names) to a one-dimensional array, enter the following code:

Sub Convert_Single_Column_to_One_Dimensional_Array()

Dim Myarray As Variant

Myarray = Application.Transpose(Range("B4:B13"))

End Sub

Note: This code develops a Macro called Convert_Single_Column_to_One_Dimensional_Array.

• To see any specific value of the array, put a line at the end mentioning a message box containing the value’s index. For example, to see the name of the 5th student, enter:

• Run the code.
• You’ll get the name of the 5th student, Shane Austin.

Converting a Single Row

Steps:

• Use the Transpose property of VBA twice.
• To convert the row B4:E4 to an array, enter:

Sub Convert_Single_Row_to_One_Dimensional_Array()

Dim Myarray As Variant

Myarray = Application.Transpose(Application.Transpose(Range("B4:E4")))

End Sub

Note: This code develops a Macro called Convert_Single_Row_to_One_Dimensional_Array.

• To see any specific value of the array, put a line at the end mentioning a message box containing the value’s index.
• For example, to see the 2nd value of the array, enter:

• Run the code.
• It’ll display 82, the 2nd value of the array.

You can use Input Box and the worksheet name in the code to give the code a bit more flexibility.

Read More: How to Create an Array in Excel VBA

Method 3 – Convert a Range to an Array by Iterating through a For-Loop

One-Dimensional Range to One-Dimensional Array

Steps:

• Enter the following VBA code to convert a single row or column to a one-dimensional column using For-Loop:

Sub Convert_Range_to_One_Dimensional_Array_by_For_Loop()

Dim Myarray() As Variant
ReDim Myarray(Range("B4:B13").Rows.Count)

i = 1

For Each j In Range("B4:B13")
    Myarray(i) = j
    i = i + 1
Next j

End Sub

Code Breakdown

• This code creates a Macro called Convert_Range_to_One_Dimensional_Array_by_For_Loop.
• It first declares an array named Myarray with the data type Variant.
• Then, it sets the dimension of the array to be equal to the number of rows in the range B4:B13. If you have to convert any other range, enter it here. Or you can use an Input Box to take the range from the user every time he/she runs it (Details in Section 1).
• This code converts a single column to an array. If you want to convert a single row instead of Rows.Count in the 3rd line, and use Columns.Count.
• Next comes the For-Loop. It iterates through each value of the range B4:B13 and assigns it to the array Myarray. Obviously, you need to enter your range instead of B4:B13 here.

• Put the line with the MsgBox at the end to show any specific array value. For example, to access the 5th value of the array, enter:

• Run the code. It’ll show the 5th element of the array, Shane Austin.

Two-Dimensional Range to Two-Dimensional Array

Steps:

Enter the following VBA code to convert a single row or column to a one-dimensional column using For-Loop:

Sub Convert_Range_to_Two_Dimensional_Array_by_For_Loop()

Dim Myarray() As Variant

ReDim Myarray(Range("B4:E13").Rows.Count, Range("B4:E13").Columns.Count)

i = 0

For Each j In Range("B4:E13")
    Myarray((Int(i / Range("B4:E13").Columns.Count)) + 1, (i Mod Range("B4:E13").Columns.Count) + 1) = j
    i = i + 1
Next j

End Sub

Code Breakdown

• This code creates a Macro called Convert_Range_to_Two_Dimensional_Array_by_For_Loop.
• It first declares an array named Myarray with the data type Variant.
• Then it sets the dimensions of the array equal to the number of rows and columns of the range B4:E13. If you have to convert any other range, enter it here. Or you can use an Input Box to take the range from the user every time he/she runs it (Details in Section 1).
• Next comes the For-Loop. It iterates through each value of the range B4:E13 and assigns it into the array Myarray. Obviously, you need to enter your range instead of B4:E13 here.

• To show any specific value of the array, put the line with the MsgBox at the end. For example, to access the value of the 5th row and 3rd column, enter:

I

• Run the code. It’ll show the value from the 5th row and the 3rd column, 57.

Two-Dimensional Range to One-Dimensional Array

Steps:

• Choose the range B4:E13.
• Enter the following VBA code:

Sub Convert_Two_Dimensional_Range_to_One_Dimensional_Array_by_For_Loop()

Dim Myarray() As Variant

ReDim Myarray(Range("B4:E13").Rows.Count * Range("B4:E13").Columns.Count)

i = 1

For Each j In Range("B4:E13")
    Myarray(i) = j
    i = i + 1
Next j

End Sub

Code Breakdown

• This code creates a Macro called Convert_Two_Dimensional_Range_to_One_Dimensional_Array_by_For_Loop.
• It first declares an array named Myarray with the data type Variant.
• Then it sets the dimensions of the array equal to the total number of values of the range B4:E13 (Number of rows * Number of Columns). Obviously, you enter your range instead of B4:E13 here. Or use an Input Box.
• Next comes the For-Loop. It iterates through each value of the range B4:E13 and assigns it into the array Myarray. No more need to say, enter your range instead of B4:E13 here.

• To show any specific value of the array, put the line with the MsgBox at the end. For example, to access the 15th value of the array:

• Run the code. It’ll show the 15th value of the array, 72 (Counting row-wise).

Read More: How to Declare Array in Excel VBA

Download the Practice Workbook

Download this workbook to practice.

Related Articles

• How to Define VBA Global Array in Excel
• Excel VBA Array of Strings