🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA ReDim in Excel — Resize Arrays with ReDim and ReDim Preserve

|

VBA ReDim in Excel — Resize Arrays with ReDim and ReDim Preserve

TL;DR — A VBA array locks its size the moment you Dim it — unless you leave the brackets empty. Dim a() declares a dynamic array with no size yet; ReDim a(1 To 10) gives it one. A plain ReDim throws away every value it held, so to grow an array without losing data you use ReDim Preserve. The catch: on a two-dimensional array, ReDim Preserve can only resize the last dimension. Fixed-size arrays (Dim a(1 To 10)) can never be ReDim-ed at all.

Sub GrowAnArray()
    Dim a() As Long                 ' dynamic - no size yet
    ReDim a(1 To 3)                 ' now it holds 3 Longs
    a(1) = 10: a(2) = 20: a(3) = 30

    ReDim Preserve a(1 To 5)        ' grow to 5, KEEP 10, 20, 30
    a(4) = 40: a(5) = 50            ' a plain ReDim here would have wiped 10-30
End Sub

ReDim is the whole story of dynamic arrays. Declare the array open with Dim a(), size it with ReDim, ask how big it is with UBound, and — for a grid — remember that only the last dimension can grow (see VBA 2D Arrays). Get ReDim wrong and your data quietly disappears, so it pays to understand when the size is fixed and when it is not.

What you'll learn

  • The one decision that governs every array — fixed size at Dim, or open with Dim a()
  • Why ReDim sizes a dynamic array, and why a plain ReDim erases everything it held
  • ReDim Preserve — how to grow an array and keep the data
  • The number-one trap: ReDim Preserve can only resize the last dimension of a 2D array
  • Why you cannot ReDim a fixed-size array, and what Erase actually does
  • Why growing an array one row at a time in a loop is slow, and what to do instead

The mental model: fixed at Dim, or open with empty brackets

Every VBA array is one of two kinds, and you choose which at declaration:

Dim fixed(1 To 10) As Long   ' FIXED - 10 slots, forever; size is part of the type
Dim flex() As Long           ' DYNAMIC - no size yet; you will ReDim it later

A fixed-size array is sized once and never changes — the size lives in the declaration, so VBA will not let you ReDim it later (you get the compile error Array already dimensioned). A dynamic array is declared with empty brackets: it has a type but no size, and ReDim is how you give it one. That single choice — brackets full or empty at Dim — decides everything else on this page. If you do not yet know how many items you will have, declare it open.

ReDim sizes a dynamic array

ReDim allocates fresh storage for a dynamic array. You can ReDim the same array as many times as you like, and each ReDim can even change the bounds:

Dim a() As String
ReDim a(1 To 3)      ' 3 elements, indexed 1..3
ReDim a(0 To 99)     ' now 100 elements, indexed 0..99

There is one thing to keep in your head, and it is the source of most "my data disappeared" bugs: a plain ReDim re-initialises the whole array. Every element goes back to its default — 0 for numbers, "" for strings, Nothing for objects — as if you had just created it:

Dim a() As Long
ReDim a(1 To 3)
a(1) = 10: a(2) = 20: a(3) = 30
ReDim a(1 To 5)      ' a(1..3) are now 0 again - the values are GONE

If you meant to keep the data, that is exactly what Preserve is for.

ReDim Preserve: grow without losing data

ReDim Preserve copies the existing values into the resized array before handing it back:

Dim a() As Long
ReDim a(1 To 3)
a(1) = 10: a(2) = 20: a(3) = 30
ReDim Preserve a(1 To 5)   ' a(1..3) still hold 10, 20, 30; a(4), a(5) are 0

Reach for Preserve whenever you are extending an array you have already filled. It works when you grow and when you shrink — shrinking simply drops the elements past the new upper bound. The one rule you must respect is about dimensions, and it is where almost everyone gets caught.

The number-one trap: Preserve only resizes the last dimension

On a one-dimensional array, ReDim Preserve is free to change the bound. On a two-dimensional array, ReDim Preserve can only change the last dimension. Try to change the first, and VBA raises run-time error 9, Subscript out of range:

Dim g() As Long
ReDim g(1 To 3, 1 To 2)          ' 3 rows, 2 columns
ReDim Preserve g(1 To 5, 1 To 2) ' error 9 - cannot resize the FIRST dimension
ReDim Preserve g(1 To 3, 1 To 4) ' OK - the LAST (column) dimension can grow

This is not a bug you can code around with a cleverer ReDim; it is how VBA stores arrays in memory. It also collides with intuition, because a sheet is rows × columns, so the "grow" you usually want — adding rows — is the first dimension, the one you are not allowed to touch. Two ways out: declare the grid as columns × rows so the dimension you grow is last, or, more commonly, size the array once to its final row count before the loop and skip Preserve entirely. If you truly must add rows to an existing 2D array, you rebuild it (or transpose, resize the last dimension, transpose back). There is more on this in VBA 2D Arrays.

You cannot ReDim a fixed-size array

ReDim is only for arrays declared with empty brackets. If you sized the array in its Dim, the size is baked into the declaration and ReDim is a compile error:

Dim a(1 To 10) As Long
ReDim a(1 To 20)      ' compile error - "Array already dimensioned"

If you find yourself wanting to resize an array, that is the signal it should have been declared dynamic (Dim a()) in the first place. A dynamic array that you ReDim once, right after you learn the count, gives you the best of both worlds: a clean fixed working size, chosen at run time.

Erase: reset or free

Erase does two different things depending on the kind of array. On a fixed-size array it resets every element to its default but keeps the array the same size. On a dynamic array it goes further — it frees the memory and returns the array to its un-sized state, as if you had only ever written Dim a():

Dim fixed(1 To 3) As Long
Erase fixed            ' all three elements are 0 again; still size 3

Dim flex() As Long
ReDim flex(1 To 3)
Erase flex             ' memory freed; flex has no size again - ReDim before reuse

After erasing a dynamic array, calling UBound on it raises error 9 until you ReDim it again — the array genuinely has no bounds to report.

Size once, do not grow one at a time

Because every ReDim Preserve allocates a new block and copies the old contents into it, growing an array by one on each pass of a loop turns an O(n) job into an O(n²) one — fine for 50 items, painful for 50,000. The idiomatic fix is to size the array once, up front, using a count you already have (a last row, a collection size), and only trim at the end if needed:

Dim a() As Long, n As Long, i As Long
n = Cells(Rows.Count, 1).End(xlUp).Row   ' how many rows of data
ReDim a(1 To n)                          ' one allocation
For i = 1 To n
    a(i) = Cells(i, 1).Value
Next i

When you genuinely cannot know the count in advance, a common pattern is to ReDim in generous chunks (say, double the size when you run out) and ReDim Preserve down to the exact count once at the end — far fewer copies than growing by one each time.

How ExcelMaster helps

The whole ReDim story is one decision — is this array a fixed size, or does it grow? — and the two classic mistakes are a plain ReDim that silently wipes your data and a ReDim Preserve that hits error 9 on the wrong dimension.

ExcelMaster lets you describe the task — "read column A into an array, keep the non-blank values, write them to column C" — and it declares the array dynamic, sizes it once from the real row count, uses ReDim Preserve only where the dimension allows it, and reads the range in a single shot instead of cell by cell. You keep the workbook and the code.

Frequently asked questions

What does ReDim do in VBA?

ReDim sets or changes the size of a dynamic array — one declared with empty brackets, such as Dim a() As Long. ReDim a(1 To 10) gives the array ten elements. You can ReDim the same array repeatedly, and each ReDim can change the bounds. A plain ReDim also re-initialises every element to its default value, so use ReDim Preserve when you need to keep the existing data.

What is the difference between ReDim and ReDim Preserve?

A plain ReDim allocates the new size and clears every element back to its default, discarding whatever the array held. ReDim Preserve copies the existing values into the resized array first, so the data survives. Use plain ReDim for the first sizing, and ReDim Preserve whenever you are extending an array you have already filled.

Why does ReDim Preserve give error 9 on a 2D array?

Because ReDim Preserve can only resize the last dimension of a multi-dimensional array. Changing the first dimension raises run-time error 9, Subscript out of range. To add "rows" to a grid, either declare it as columns × rows so the growing dimension is last, size it to its final row count before the loop, or rebuild the array.

Can I ReDim a fixed-size array?

No. If you declared the array with a size, such as Dim a(1 To 10), that size is part of the type and ReDim is a compile error, Array already dimensioned. Only arrays declared with empty brackets (Dim a()) can be sized and resized with ReDim.

How do I add an item to a VBA array?

Track the count yourself and ReDim Preserve to make room: ReDim Preserve a(1 To n + 1): a(n + 1) = value: n = n + 1. This works, but resizing on every item is slow because each ReDim Preserve copies the whole array. For large loops, size the array once from a known count, or grow it in chunks and trim once at the end. A Collection is often simpler when the count is genuinely unknown.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-24.

Related guides: VBA UBound · VBA 2D Arrays · VBA Array · VBA Dim · VBA For Loop