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

In this article, I’ll show you how you can convert a range to an array in VBA in Excel.

Convert Range to Array in Excel VBA (Quick View)

Sub Convert_Range_to_Two_Dimensional_Array()

Dim Myarray As Variant

Myarray = Range("B4:E13")

End Sub

How to Convert a Range to an Array in Excel VBA: 3 Easy Ways

Here we’ve got a data set with the names of some students, and their marks in Physics, Chemistry, and Mathematics, in a school called Sunflower Kindergarten.

Our today’s objective is to convert the range of this data set into an array in Excel VBA.

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

First, we’ll convert a range into a two-dimensional array.

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

First, declare the name of the array with the data type Variant.

Here, I’ve declared it as Myarray.

Then assign your desired range (B4:E13 in this example) to it using the Range property of VBA.

Therefore, 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.

⧭ Output:

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:

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

⧭ More Learnings:

➤ 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.

Instead of the line:

You can use:

If you run this code, it’ll first ask for the range.

Enter your preferred range there and then click on OK.

Here I’ve entered B4:E13.

It’ll convert the range to an array.

➤ Also, this code works only for the range of the active worksheet.

You can fix the name of the worksheet before the Range object to give it a bit more rigidity.

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

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

Read More: How to Use Arrays Instead of Ranges in Excel VBA

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

The Range property of VBA turns a range into a two-dimensional array by default.

But sometimes you may need to convert a single column or a single row to a one-dimensional array.

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

⧪ Converting a Single Column:

For converting a single column, you have to wrap the column within the Transpose property of VBA only once.

For example, to convert the array B4:B13 (Student Names) to a one-dimensional array, use:

So, the complete VBA code will be:

⧭ VBA 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.

⧭ Output:

To see any specific value of the array, put a line at the end mentioning a message box containing the index of the value.

For example, to see the name of the 5th student put:

Now run the code. And you’ll get the name of the 5th student, Shane Austin.

⧪ Converting a Single Row:

To convert a single row to an array, you have to use the Transpose property of VBA twice.

For example, to convert the row B4:E4 to an array, use:

So, the complete VBA code will be:

⧭ VBA Code:

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.

⧭ Output:

To see any specific value of the array, put a line at the end mentioning a message box containing the index of the value.

For example, to see the 2nd value of the array, use:

Then run the code. And it’ll display 82, the 2nd value of the array.

⧭ More Learnings:

Obviously, you can use Input Box and the worksheet name in the code to give the code a bit more flexibility. See section 1 for details.

Read More: How to Create an Array in Excel VBA

3. Convert a Range to an Array by Iterating through a For-Loop (Applicable for both One-Dimensional and Two-Dimensional Arrays)

You can also convert a range to an array using the For-Loop of VBA. This method applies to both one-dimensional and two-dimensional arrays.

⧪ One-Dimensional Range to One-Dimensional Array:

You can use the following VBA code to convert a single row or column to a one-dimensional column using For-Loop.

⧭ VBA Code:

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

⧭ Notes:

• 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 equal to the number of rows of 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, use Columns.Count.
• Next comes the For-Loop. It iterates through each value of the range B4:B13 and assigns it into the array Myarray. Obviously, you need to enter your range instead of B4:B13 here.

⧭ Output:

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

Now, run the code. It’ll show the 5th element of the array, Shane Austin.

⧪ Two-Dimensional Range to Two-Dimensional Array:

You can use the following VBA code to convert a single row or column to a one-dimensional column using For-Loop.

⧭ VBA Code:

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

⧭ Notes:

• 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.

⧭ Output:

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

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

⧪ Two-Dimensional Range to One-Dimensional Array:

This is the most useful feature of using the For-Loop.

You can produce a one-dimensional array from a two-dimensional range by using the For-Loop, which the first two methods can’t accomplish.

Let’s produce a one-dimensional array from the range B4:E13. You can use the following VBA code:

⧭ 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

⧭ Notes:

• 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.

⧭ Output:

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:

Now, 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 Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Using these methods, you can convert a range to an array in VBA in Excel. Do you have any questions? Feel free to ask us.

Related Articles

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