How can I “ReDim Preserve” a 2D Array in Excel 2007 VBA so that I can add rows, not columns, to the array?

Tag: excel , vba Author: nightcherry541597 Date: 2010-10-05

I'm working with a dynamic array in Excel VBA. The number of columns (m) is fixed, however, I do not know how many rows (n) will be required.

The help documents state that ReDim Preserve myArray(n, m) allows me to make m larger, but not n. However, I need to increase the number of rows (n) while preserving my data, not columns (m)!

For example, I may have a (5,20) array that I would like to expand to (10,20) while preserving my data.

It seems that if there were some way to transpose my array, do a ReDim Preserve to expand the number of "columns", then re-transpose my array, I could accomplish what I want.

Is this the correct way to do this? If so, how can I do that?

Is there a better way to accomplish what I want?

Other Answer1

One way to do what you want is to use a 1-D array that contains 1-D arrays instead of a 2-D array. Then you can ReDim Preserve the outer array all you want. If you're returning the outer array from a function, Excel will do the right thing and coerce it to a 2-D array.

For example, the function below will return a 3x2 array to the cells it's called from:

Public Function nested()
    Dim outer
    outer = Array(Array(1, 2), Array(3, 4))

    ReDim Preserve outer(1 To 3)

    outer(3) = Array(5, 6)

    nested = outer
End Function

My answer to these questions might also be useful to you: click me and

Of course, if you're not returning this from a UDF, you'll have to coerce it yourself. An easy way to do that without writing looping code is to do this:

Dim coerced
coerced = Application.Index(outer, 0, 0)

This is just calling Excel's built-in INDEX function, and the zeros mean that you want back all of your rows and all of your columns. Excel will coerce your 1-D array of 1-D arrays to a 2-D array automatically. (Caveat: there are some size limitations, but they are much bigger than 10x20.)


I didn't end up using or trying this because my solution was good enough for my needs this time. At first glance, this does look like a more elegant and efficient way to do what I wanted.

Other Answer2

The word 'transpose' immediately leaps to mind. You could simply enter data into the 2D array by flipping the columns and rows (i.e. transpose), effectively allowing you to make n (now the number of columns, but storing row values) larger when you require.

To reference the values, say in a double loop, swap the indices around. E.g. rather go from i = 1 to n and j = 1 to m where you reference value(i, j) , use i = 1 to m and j = 1 to n.

Other Answer3

One way how you could sove it is indeed by a double transpose with a change on the number of columns in between. This will however only work for two-dimensional arrays. It is done as follows:

' Adding one row is done by a double transposing and adding a column in between.
' (Excel VBA does not allow to change the size of the non-last dimension of a
' multidimensional array.)
myArray = Application.Transpose(myArray)
ReDim Preserve myArray(1 To m, 1 To n + 1)
myArray= Application.Transpose(myArray)

Of course m and n can be deduced as follows:

m = UBound(myArray, 1)
n = UBound(myArray, 2)

So you use the built-in transpose functionality of Excel itself. As mentioned in the code comments, this will not work for higher order matrices.


Thanks. I'm amazed how fast this is. 0.08sek for 16200 rows * 14 columns = 226000 values (2x transpose, 1x redim)

Other Answer4

Solved my own question; here's how I got around my problem. I created a temporary array, copied the contents of myArray to the temporary Array, resized myArray, then copied the contents back from the temp array to myArray.

tempArray = myArray
ReDim myArray(1 To (UBound(myArray()) * 2), 1 To m)
For i = 1 To n
     For j = 1 To m
          myArray(i, j) = tempArray(i, j)
     Next j
Next i

If anyone can suggest a more efficient way to do this, I'd love to hear it.


-1: Two nested cycles for every redim of an array - not a bright idea at all! What is going to happen if you have an array of 10 000 elements?

Other Answer5

An array with 2 dimensions, where the number of columns are fixed and the number of rows are dynamic, can be created like this:

Sub test2DimArray()
Dim Arr2D() As String
Dim NumberOfCol As Long
Dim I As Long, J As Long, x As Long
Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String

NumberOfCol = 3
J = 1
Debug.Print "Run " & Now()
Debug.Print "Sheet content"
Debug.Print "Row   col1     col2     col3"

For I = 1 To 10
tmpValue = Cells(I, 1).Value
tmpValue2 = Cells(I, 2).Value
tmpValue3 = Cells(I, 3).Value
Debug.Print I & " =    " & tmpValue & "     " & tmpValue2 & "     " & tmpValue3
    If Len(tmpValue) > 0 Then
        ReDim Preserve Arr2D(NumberOfCol, 1 To J)
        Arr2D(1, J) = tmpValue
        Arr2D(2, J) = tmpValue2
        Arr2D(3, J) = tmpValue3
        J = J + 1
    End If

'check array values
Debug.Print vbLf; "arr2d content"
Debug.Print "Row   col1     col2     col3"

For x = LBound(Arr2D, 2) To UBound(Arr2D, 2)
Debug.Print x & " =   " & Arr2D(1, x) & "        " & Arr2D(2, x) & "        " & Arr2D(3, x)

Debug.Print "========================="
End Sub

TempValue read from cells A1:A10, if there is a value in cell Ax, it redim the array with +1, and add Tempvalue to array col1, add contents in Bx to array col2 and contents in Cx to array col3. If length of Ax-value is 0, it does not add anything to the array.

Debug.print show results in the "immediate window" in the VB editor.

Without the testing lines, and adding a dynamic data-range the code can be:

Sub my2DimArray()
Dim Arr2D() As String
Dim NumberOfCol As Long, NumberOfRow As Long
Dim FirstCol As Long, FirstRow As Long, LastCol As Long, LastRow As Long
Dim I As Long, J As Long, X As Long
Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String

'if cells with values start in A1
With ActiveSheet.UsedRange
    NumberOfCol = .Columns.Count
    NumberOfRow = .Rows.Count
End With

'if cells with values starts elsewhere
With ActiveSheet.UsedRange
    FirstCol = .Column
    FirstRow = .Row
    LastCol = .Column + .Columns.Count - 1
    LastRow = .Row + .Rows.Count - 1
End With

J = 1

For I = 1 To NumberOfRow 'or For I = FirstRow to LastRow
tmpValue = Cells(I, 1).Value 'or tmpValue = Cells(I, FirstCol).Value
    If Len(tmpValue) > 0 Then
        ReDim Preserve Arr2D(NumberOfCol, 1 To J)
            For X = 1 To NumberOfCol 'or For X = FirstCol to LastCol
                Arr2D(X, J) = Cells(I, X).Value
            Next X
        J = J + 1
    End If
Next I

End Sub

Other Answer6

No way to determine the number of elements in the first dimension? Bummer. For a two-dimensional array with a fixed second dimension, you might want to consider making it an array of Types ("structs" in other languages) instead. That will allow you to use Redim Preserve, and still leaves you with a reasonable way to add and access values, though you'll now be accessing the second dimension as named members of the Type rather than is index values.

Other Answer7

coercing or Slicing doesnt seem to work with Index( or Match(Index( when i want to filter array (w/o loops) based on multiple criteria, when the size of data spans greater than 2^16 rows (~ 92000 rows).

Run-Time error '13':

Type Mismatch

Transpose doesnt work with large recordsets and so also double Transpose does not work. isn't there anyway to filter an array and grab data without resorting to multiple loops?

I am thinking of trying the dictionary way or ADO with Excel.

Other Answer8

If you are developer - what is the difference between rows and columns? Using array(N, 2) (if you have 2 columns) is the same as array(2, N) - for which you can

ReDim Preserve arr(1 to 2, 1 to N+1). 

And the difference for you (as developer) will be to put the variable from the cycle in second place, instead of the first one:

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(1, i)
    GetColumn2Value = arr(2, i)

Or you want this:

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(i, 1)
    GetColumn2Value = arr(i, 2)

What is the difference?