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

VBA Resize in Excel — Reshape a Range from Its Anchor (and Why It's a Count, Not a Delta)

|

VBA Resize in Excel — Reshape a Range from Its Anchor (and Why It's a Count, Not a Delta)

TL;DR — Resize(rows, columns) keeps a range's top-left anchor fixed and redraws the rectangle to be exactly that many rows tall and columns wide. It does not move the reference (that is Offset) and it does not select — it returns a new range at the same corner. The catch: the arguments are an absolute, 1-based count of the final size, not a delta. Range("A1").Resize(5, 3) is A1:C5 (5 rows, 3 columns total); Resize(0, ...) throws error 1004. Omit an argument to leave that dimension unchanged.

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
' Anchor stays at A1; the rectangle is redrawn to 5 rows by 3 columns.
ws.Range("A1").Resize(5, 3).Select        ' selects A1:C5
' Omit the column count to keep the width and just grow the height:
ws.Range("A1:D1").Resize(10).Select       ' A1:D10 (width stays 4)

Resize is how a macro stops hardcoding "A1:D100" and starts fitting the reference to whatever the data actually is. It is a small method with one big misconception attached: people read Resize(5) as "add five rows" when it means "be five rows." This guide is built on the idea that makes every later rule fall out: Resize pins the anchor and states the final size. Hold that, and the count-not-delta rule, the omitted arguments, and the header-drop idiom all stop being surprises.

What you'll learn

  • The mental model — Resize pins the top-left anchor and redraws the rectangle
  • The rule that matters most — the arguments are a total count, not a delta to add
  • Omitting an argument to leave one dimension unchanged
  • Why the anchor is always the top-left, even on a multi-cell range
  • The two patterns that pay off — dropping a header, and writing an array to a fitted block
  • Where Resize fits next to Offset and CurrentRegion

The mental model: Resize pins the anchor and redraws the rectangle

Every range has a top-left cell — its anchor. Resize leaves that anchor exactly where it is and redraws the rectangle to the size you give:

ws.Range("B2").Resize(3, 4)   ' anchor B2 stays; new range is B2:E4 (3 rows, 4 columns)

Picture grabbing the bottom-right handle of a selection and dragging: the top-left corner is pinned, and the size changes under your hand. That is the whole method. Two things it is not: it is not Offset, which slides the whole reference to a new position without changing its size; and it is not a selection or an action — Resize returns a range, and you have to use the result (.Value =, .Select, Set r = ...). A Resize call sitting alone on a line does nothing at all.

The rule that matters most: it's a count, not a delta

This is the misconception that produces wrong-sized ranges. Resize(rows, columns) is the absolute, 1-based total of the final rectangle — not an amount to add to the current size:

ws.Range("A1").Resize(5, 3)   ' A1:C5  -> 5 rows and 3 columns TOTAL, not "5 more rows"
ws.Range("A1").Resize(1, 1)   ' A1     -> a single cell (1 row, 1 column)

So Resize(1) is one row, not "shrink by one." And because a range cannot have zero or negative size, Resize(0, 3) or Resize(-1, 3) throws run-time error 1004. If you are computing a row count, guard it: a data block with only a header row gives Rows.Count - 1 = 0, and feeding that to Resize crashes. Check for the empty case before you resize.

Omit an argument to leave a dimension alone

Both arguments are optional, and an omitted one means "leave that dimension unchanged." This is the most common real-world use — fix one side, change the other:

ws.Range("A1:D1").Resize(10)        ' rows -> 10, columns stay at 4  => A1:D10
ws.Range("A1:D1").Resize(, 6)       ' columns -> 6, rows stay at 1   => A1:F1

Note the bare comma in Resize(, 6) — that is how you skip the first argument and set only the second. Reading Resize(10) as "10 rows, same width" is exactly right, and it is why growing a header row into a full table is a one-liner.

The anchor is always the top-left

Resize computes from the anchor and ignores the existing size except for that corner. Call it on a multi-cell range and the old dimensions are gone:

ws.Range("B2:D10").Resize(1, 1)   ' collapses to B2 - the anchor only

That surprises people who expect Resize to adjust their existing block relative to its current size. It does not — it restates the size from the top-left. If you want to reshape from the first cell of a range deliberately, that is already what happens; if you accidentally called it on a big range, you just threw the size away. When you need the anchor explicitly, rng.Cells(1, 1) (see VBA Cells) gives you the top-left cell to resize from.

The two patterns that pay off

Almost every useful Resize is one of these two. First, dropping a header row — pair it with Offset, because Offset alone keeps the same height and would spill one row past the bottom:

Dim tbl As Range: Set tbl = ws.Range("A1").CurrentRegion   ' whole block incl. header
Dim body As Range
' Move down past the header, THEN shrink the height by one so it doesn't overrun:
Set body = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count)

Offset moves, Resize reshapes — together they carve the data body out of a block. Second, writing an array back to a block that fits it. Assigning an array to a single unresized cell fills only that cell; resize the anchor to the array's dimensions first:

Dim data(1 To 100, 1 To 3) As Variant
' ... fill data ...
ws.Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data   ' fills A1:C100 in one shot

That single assignment is orders of magnitude faster than looping cell by cell, and Resize is what makes the target match the data exactly.

Offset moves, Resize reshapes

Keep the pair straight and dynamic ranges stop being fiddly: Offset changes where a reference is; Resize changes how big it is. You reposition with one and reshape with the other, and most real work uses both — find an anchor, offset past what you want to skip, resize to fit what is left. Combine that with CurrentRegion to detect the block in the first place, and you have the whole toolkit for referring to data whose size you do not know in advance — without ever typing a hardcoded address that breaks the day the data grows by a row. The shape lives in the data; Resize just fits the reference to it.

How ExcelMaster helps

Resize hides a handful of quiet traps: the count-versus-delta misread that produces a wrong-sized block, an error 1004 when a computed row count hits zero, an anchor that discards your existing size, and the array assignment that fills one cell because the target was never resized. None of them raise a helpful message at the moment you make them.

ExcelMaster lets you describe the result instead. Say "write this table to the sheet starting at A1" or "select the data without the header row," and it resizes the anchor to the array's exact dimensions, or offsets past the header and resizes the height down by one — guarding the empty-block case so nothing throws. You keep the workbook and the code; you skip the debugging pass where only the first cell filled.

Frequently asked questions

What does Resize do in Excel VBA?

Resize(rows, columns) returns a new range that keeps the original range's top-left cell (its anchor) and is exactly rows tall and columns wide. It does not move the reference and it does not select anything on its own — you use the returned range, for example Range("A1").Resize(10, 3).Value = data.

Is Resize a delta or an absolute size?

It is an absolute, 1-based count of the final size, not an amount to add. Range("A1").Resize(5, 3) is A1:C5 (five rows and three columns in total), and Resize(1, 1) is a single cell. Because a range cannot be empty, Resize(0, ...) or a negative count throws run-time error 1004.

What is the difference between Resize and Offset in VBA?

Offset moves a range to a new position without changing its size; Resize changes the size while keeping the top-left anchor fixed. They are complementary and often used together, such as rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) to skip a header row and then trim the height so the result does not overrun.

How do I write an array to a range with Resize?

Resize the anchor cell to match the array's dimensions, then assign in one statement: Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data. Assigning an array to a single unresized cell fills only that cell, so the Resize is what makes the whole block receive the data, and it is far faster than a cell-by-cell loop.

How do I resize only the number of rows and keep the columns?

Omit the second argument: Range("A1:D1").Resize(10) sets the height to 10 rows and leaves the width at 4, giving A1:D10. To change only the columns, skip the first argument with a bare comma: Range("A1:D1").Resize(, 6) gives A1:F1.

Tested in

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

Related guides: VBA Offset · VBA CurrentRegion · VBA Cells · VBA Range · VBA For Loop