How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)

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.


Download Practice Workbook


2 Handy Approaches to ReDim Preserve 2D Array in Excel VBA

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.

Excel VBA ReDim Preserve 2D Array


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.

Steps:

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

Excel VBA ReDim Preserve 2D Array 2

  • 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

Excel VBA ReDim Preserve 2D Array 3

VBA Code Breakdown

  • Firstly, we are calling the Sub procedureRedim_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.
  • After that, we will execute the code.
  • So, Save the Module and press Run.

Excel VBA ReDim Preserve 2D Array 4

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

Excel VBA ReDim Preserve 2D Array 5

  • 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

Excel VBA ReDim Preserve 2D Array 6

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

Excel VBA ReDim Preserve 2D Array 7

  • 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

Excel VBA ReDim Preserve 2D Array 8

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

Sample Dataset

Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)


Similar Readings


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.

Error Message Box

Steps:

    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

ReDim Multi Dimensions

VBA Code Breakdown

  • Firstly, we are calling the Sub procedureReDim_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.

Final Output

Read More: VBA to Transpose Array in Excel (3 Methods)


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.

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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo