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

VBA Code to Convert Range to Array in Excel


Download Practice Workbook

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


3 Ways to Convert a Range to an Array in Excel VBA

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.

Data Set to Convert Range to Array in Excel VBA

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.

Dim Myarray as Variant

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

Myarray = Range("B4:E13")

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.

VBA Code to Convert Range to Array in Excel

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:

MsgBox (5,3)

 

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

Output of Turing Range to Array in Excel VBA

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:

Myarray = Range("B4:E13")

You can use:

Rng = InputBox("Enter the Range to Convert to Array: ")

Myarray = Range(Rng)

VBA Code to Change Turing Range to Array in Excel VBA

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:

Myarray = Worksheets("Sheet1").Range("B4:E13")

VBA Code to Convert Turing Range to Array in Excel VBA

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

Read More: How to Use VBA to Set a Range Variable to Selection in Excel (5 Methods)


Similar Readings


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:

Dim Myarray As Variant

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

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.

VBA Code to Convert Turing Range to Array in Excel VBA

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:

MsgBox Myarray(5)

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

Output of Converting Turing Range to Array in Excel VBA

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:

Dim Myarray As Variant

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

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.

VBA Code to Convert Turing Range to Array in Excel VBA

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:

MsgBox Myarray(12)

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

Output of Changing Turing Range to Array in Excel VBA

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.


Similar Readings


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 is applicable for 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

VBA Code to Convert Turing Range to Array in Excel VBA

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:

MsgBox Myarray(5)

VBA Code to Change Turing Range to Array in Excel VBA

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

VBA Code to Convert Turing Range to Array in Excel VBA

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:

MsgBox Myarray(5,3)

I

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

Output of Changing Turing Range to Array in Excel VBA

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

VBA Code to Convert Turing Range to Array in Excel VBA

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:

MsgBox Myarray(15)

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

Read More: VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1 Comment
  1. You may want to enhance your code to handle ranges with multiple areas. In my experiments, direct assignment of a multi-area range to an array does not work BUT also does not cause an error (in Excel 2016). I had hoped it would return a 3-dimension array, but it does not.

Leave a reply

ExcelDemy
Logo