An array is a variable that keeps the same kinds of data. If there is only one row or one column of data, then it is known as a one-dimensional array. However, when there is more than one row and column, it is called a 2D array. We use ReDim to resize an array in VBA. Additionally, we use the Preserve keyword with the ReDim to keep the old data intact. This article will show you 2 quick ways to “ReDim Preserve” a 2D array in Excel VBA.
How to ReDim Preserve 2D Array in Excel VBA: 2 Handy Approaches
This is the base dataset created from a 2D array with three rows and two columns. Firstly, we will create this array. Then, we will add another column to this array. We will use the “ReDim Preserve” to do so. Additionally, we will demonstrate what happens if we don’t use this.
By default, we can only resize the last dimension of the array (i.e. the columns or the upper bound). We will transpose the array, then change the last dimension, and then transpose again to resize both dimensions of the 2D array in Excel VBA.
1. ReDim Preserve Last Dimension 2D Array
We will first define the 2D array as dynamic. Then, using the ReDim statement, we will create an array with three rows and two columns. Lastly, we will use the ReDim statement again with the Preserve keyword to increase the upper bound of the two dimensional array.
- To begin with, press ALT+F11 to bring up the VBA Module window. Alternatively, you can do this from the Developer tab → select Visual Basic.
- Then, from the Insert tab → select Module. We will type the VBA code here.
- Next, type the following code in the Module window.
Sub Redim_Preserve_2D_Array_Row()
Dim Our_Array() As Variant
ReDim Our_Array(1 To 3, 1 To 2)
Our_Array(1, 1) = "Rachel"
Our_Array(2, 1) = "Ross"
Our_Array(3, 1) = "Joey"
Our_Array(1, 2) = 25
Our_Array(2, 2) = 26
Our_Array(3, 2) = 25
Range("C6:D8").Value = Our_Array
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure “Redim_Preserve_2D_Array_Row”.
- Then, we declare the variable Our_Array as a dynamic array.
- Next, we define the size of the array. The lower bound is 3, the upper bound is 2, and both start from 1.
- Then, we assign values to the array.
- After that, we input the values to the C6:D8 cell range.
- As a result, it will return the values to the defined cell ranges. We can see that “Rachel” is in row 1 and column 1 position, which was defined as (1,1) in the VBA code.
- Now, we will resize the array.
- So, add this to the previous code and remove the first Range.Value statement. Moreover, how the code looks like you can see from the snapshot below.
ReDim Our_Array(1 To 3, 1 To 3)
Our_Array(1, 3) = "Texas"
Our_Array(2, 3) = "Mississippi"
Our_Array(3, 3) = "Utah"
Range("C6:E8").Value = Our_Array
- Here, we have increased the upper bound from (1 To 2) to (1 To 3) by 1.
- Then, we have added the values to the array.
- Now if we execute this code, then we will see the previous values are not preserved. It will return blank for the previous values.
- Now, we can fix this by adding the Preserve keyword into the ReDim statement.
- Finally, our full code will be this.
Option Explicit
Sub Redim_Preserve_2D_Array_Row()
Dim Our_Array() As Variant
ReDim Our_Array(1 To 3, 1 To 2)
Our_Array(1, 1) = "Rachel"
Our_Array(2, 1) = "Ross"
Our_Array(3, 1) = "Joey"
Our_Array(1, 2) = 25
Our_Array(2, 2) = 26
Our_Array(3, 2) = 25
ReDim Preserve Our_Array(1 To 3, 1 To 3)
Our_Array(1, 3) = "Texas"
Our_Array(2, 3) = "Mississippi"
Our_Array(3, 3) = "Utah"
Range("C6:E8").Value = Our_Array
End Sub
- Now, if we Run this code, then the output will be like this. Thus, we will “ReDim Preserve” the last dimension of a 2D array in Excel VBA.. Now, the next method will show you how to “ReDim Preserve” and resize both dimensions of the array.
Read More: How to Redim 2D Array with VBA in Excel
2. ReDim Preserve Both Dimensions 2D Array in Excel VBA
In this final method, we will show you the steps to resize and “ReDim Preserve” the 2D array. Here, we will be using the VBA Transpose function to resize the lower bound of the array. If we tried to resize the lower bound of the array in the first method, then we will see the “Subscript out of range” error. Now, without further ado, let us see how we can fix this and achieve our goal.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, add the following lines of code to the first code.
Our_Array = Application.Transpose(Our_Array)
ReDim Preserve Our_Array(1 To 3, 1 To 4)
Our_Array = Application.Transpose(Our_Array)
Our_Array(4, 1) = "Monica"
Our_Array(4, 2) = 26
Our_Array(4, 3) = "New Mexico"
Range("C6:E9").Value = Our_Array
- Moreover, the code for the final method looks like this.
Option Explicit
Sub ReDim_Preserve_2D_Array_Both_Dimensions()
Dim Our_Array() As Variant
ReDim Our_Array(1 To 3, 1 To 2)
Our_Array(1, 1) = "Rachel"
Our_Array(2, 1) = "Ross"
Our_Array(3, 1) = "Joey"
Our_Array(1, 2) = 25
Our_Array(2, 2) = 26
Our_Array(3, 2) = 25
ReDim Preserve Our_Array(1 To 3, 1 To 3)
Our_Array(1, 3) = "Texas"
Our_Array(2, 3) = "Mississippi"
Our_Array(3, 3) = "Utah"
Our_Array = Application.Transpose(Our_Array)
ReDim Preserve Our_Array(1 To 3, 1 To 4)
Our_Array = Application.Transpose(Our_Array)
Our_Array(4, 1) = "Monica"
Our_Array(4, 2) = 26
Our_Array(4, 3) = "New Mexico"
Range("C6:E9").Value = Our_Array
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure “ReDim_Preserve_2D_Array_Both_Dimensions”.
- Then, the rest of the codes up to the VBA Transpose function are the same as in the first code.
- Here, we are transposing the array.
- Then, we are increasing the upper bound of the array.
- After that, we transpose the array again. Therefore, ultimately it will change the lower bound.
- Next, we input the values for the resized array while preserving the old data.
- Lastly, we write the values to the cell range C6:E9.
- After that, as shown in the first method, Run this code.
- Thus, we can visualize how the code preserves a 2D array using the “ReDim Preserve” and the VBA Transpose function.
Read More: Excel VBA 2 Dimensional Array Initialization
Things to Remember
- ReDim Preserve cannot change the lower bound of the array. To do so, we need to use the Transpose function.
- We can only use ReDim on dynamic arrays.
Download Practice Workbook
Conclusion
We have shown you two quick ways to “ReDim Preserve” a 2D array in Excel VBA. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!
