VBA Range.Resize: Change a Range’s Dimensions in Excel

In Excel VBA, use the Resize property to return a range with a different number of rows or columns: for example, Range("A1").Resize(3, 2) refers to A1:B3. It keeps the original range’s top-left cell as the anchor; it does not alter the worksheet until you use the returned range in an operation such as assigning a value.

What Range.Resize does

Range.Resize is a property of a Range object. It returns another Range object whose size is set by the row and column counts you supply. The original range is not expanded, contracted, or otherwise changed by calling the property. You can assign the returned object to a Range variable, use it as the source or destination of a value assignment, or call a method such as ClearContents on it.

The top-left cell of the original range stays fixed. If the original reference is A1 and you resize it to three rows and two columns, the result is A1:B3. If the original reference is B2:C3, resizing it to three rows and four columns returns B2:E4. It does not add rows around the original selection or resize it proportionally.

This distinction matters: a line such as Set target = source.Resize(5, 1) only creates a reference in memory. It does not insert worksheet rows, change column widths, or copy data. Those effects require separate operations.

Syntax and omitted arguments

The syntax is expression.Resize(RowSize, ColumnSize). Both arguments are optional, but if you specify only one, VBA treats it as the row count. To set only the column count, use the named argument ColumnSize so there is no ambiguity.

someRange.Resize(4, 3) returns a range four rows high and three columns wide.
someRange.Resize(4) sets the height to four rows and retains the original number of columns.
someRange.Resize(ColumnSize:=3) retains the original number of rows and sets the width to three columns.
someRange.Resize with no arguments returns a range with the original dimensions, so it is usually unnecessary.

For example, resizing A1:B2 with Resize(4) returns A1:B4: the row count changes, while the original two-column width remains. Using Resize(ColumnSize:=3) on that same range returns A1:C2.

A basic VBA example

This macro writes a two-dimensional block of values to A1:C3. It demonstrates that the dimensions of the destination should match the dimensions of the values being assigned.

Sub WriteThreeByThreeBlock()
    Dim ws As Worksheet
    Dim target As Range
    Dim values As Variant

    ' Use the active worksheet for this example.
    Set ws = ActiveSheet
    values = Array(Array("Item", "Qty", "Price"), _
                   Array("Pencils", 12, 0.5), _
                   Array("Paper", 3, 4.25))

    ' A1 is the anchor; resize to three rows and three columns.
    Set target = ws.Range("A1").Resize(3, 3)
    target.Value = values
End Sub

Before running a macro that writes to a workbook, save a copy: VBA changes cannot be undone with Ctrl+Z. This example uses the active worksheet, so confirm the intended sheet is active before running it. In production code, assigning the specific worksheet by name is safer than relying on whichever sheet happens to be selected.

Using Resize with a starting cell or range

A common pattern is to start at a known cell and calculate the destination size. The next example places a header and a variable number of data rows into a table-shaped area. The row count is calculated from the number of array rows, with one additional row for the header.

Sub WriteRowsToSheet()
    Dim ws As Worksheet
    Dim data As Variant
    Dim output As Range
    Dim rowCount As Long

    ' Example data has two rows and three columns.
    data = Array(Array("A", "B", "C"), _
                 Array(10, 20, 30))
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Include one header row plus the two data rows.
    rowCount = UBound(data) - LBound(data) + 1
    Set output = ws.Range("A1").Resize(rowCount, 3)
    output.Value = data
End Sub

Here, the array contains two rows, so rowCount is 2 and the destination is A1:C2. A frequent source of errors is calculating dimensions from the wrong array bounds or assuming every array starts at index zero. Using LBound and UBound makes the row count explicit. For a two-dimensional array, check each dimension separately when both the row and column counts are variable.

Combining Resize with Offset

Resize changes the dimensions relative to the current range’s top-left cell. Offset moves a range reference without changing its dimensions. Combining them lets a macro choose both a starting position and a size.

For instance, Range("A1").Offset(1, 0).Resize(3, 2) refers to A2:B4: the offset moves the anchor down one row, then Resize creates a three-row, two-column reference from that new anchor. This is useful when a header occupies row 1 and a macro needs to address the data beneath it.

When building a reference from another range, remember that Offset counts rows and columns from the original reference. An offset of one row and two columns from B2 starts at D3. Resize then sets the dimensions from D3, not from the original B2. This separation of movement and sizing makes chained expressions easier to troubleshoot if each intermediate range is first assigned to a variable.

Finding a range size from worksheet data

Resize is often paired with a last-row calculation to select a changing block of records. This example assumes headers are in row 1, records are in column A, and the data has three columns. It safely avoids creating a data range when there are no records below the header.

Sub SelectDataBlock()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Only create the data range when records exist below the header.
    If lastRow > 1 Then
        Set dataRange = ws.Range("A2").Resize(lastRow - 1, 3)
        dataRange.Select
    End If
End Sub

If the last used record is on row 11, then lastRow - 1 is 10, and the range starting at A2 becomes A2:C11. This method depends on column A being populated for every record. If that column can contain blanks, it may report an earlier row than the actual end of the dataset. Choose a consistently filled key column or use a more suitable data-boundary strategy for the workbook.

Also consider whether the worksheet contains totals below the records or unrelated content in the same column. A last-row calculation is only as reliable as the data layout it assumes. For structured Excel tables, the table’s DataBodyRange can be a clearer way to refer to data rows, especially when the table grows over time.

Common errors and edge cases

Wrong anchor: Resize starts at the original range’s top-left cell. If the desired range should begin elsewhere, move the reference with Offset or start from the correct cell.
Dimensions do not match the values: When assigning an array to a range, the array and destination should have compatible row and column counts. A mismatch can cause an error or an unintended result.
Range extends past the worksheet edge: A resized range must fit within the worksheet. For example, asking for several columns from a cell near the last worksheet column can exceed the available grid and produce an error.
Unexpected last-row result: A last-row formula based on one column will miss records if that column has blanks. Confirm that the chosen column is populated throughout the data.
Confusing Resize with inserting cells: Resize returns a reference; it does not add worksheet cells or shift existing data.
Clearing more cells than intended: A call such as someRange.Resize(100, 5).ClearContents clears values and formulas in the resulting area. Verify the anchor and dimensions before using a destructive method.

Use positive row and column sizes that make sense for the intended range. When the size comes from a calculation, validate it before calling Resize, particularly if an empty dataset could produce zero rows. Handle the empty case explicitly rather than assuming a valid data range always exists.

Resize compared with related techniques

Choose the method that matches the change you want. Resize sets a range’s height and width while preserving its top-left anchor. Offset moves a range while preserving its size. Range("A1:C10") directly names a fixed area, which is straightforward when the dimensions never change. CurrentRegion selects a contiguous block around a cell, but blank rows or columns can split that block.

Resize is not the same as the worksheet’s row or column resizing commands, and it is not the same as changing a table’s size. If the goal is to insert worksheet rows, use an insertion operation. If the goal is to add records to an Excel table, use the table’s list rows or resize features. If the goal is merely to refer to a larger destination for a copy or assignment, Resize is often appropriate.

For a cell-by-cell copy, a destination range can be sized to match a source range, for example by using the source’s row and column counts. This keeps the two areas aligned without hard-coding the destination’s dimensions. However, copying values, formulas, formatting, and other cell properties are separate choices; Resize itself performs none of those actions.

Running VBA on Windows and Mac

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In Excel for Microsoft 365 for Mac, Option+F11 may open it; depending on the keyboard, you may also need to hold Fn. The Visual Basic Editor and macro availability can differ by platform, and Excel for the web does not run VBA macros.

To run a procedure, use the editor’s Run command or run it from Excel’s macro interface. On Windows, Alt+F8 opens the Macro dialog. On Mac, use Tools > Macro > Macros if available in the current Excel interface. Macro security settings may block a procedure until the workbook is trusted or macros are enabled. Only enable macros from sources you trust, and keep a backup before a macro edits or clears worksheet content.

Frequently Asked Questions

Does Range.Resize change the original range?
No. It returns a new Range reference with the requested dimensions. The worksheet changes only if your code then writes to, formats, selects, clears, or otherwise acts on that returned range.

How do I resize a range by rows but keep its current columns?
Pass the row count alone, such as myRange.Resize(5). The result has five rows and the same number of columns as myRange.

How do I resize only the number of columns?
Use the named argument myRange.Resize(ColumnSize:=4). The named argument makes clear that four is the column count; the original row count is retained.

Can Resize make a range smaller?
Yes. It can return a smaller range as well as a larger one. For example, resizing A1:D10 to two rows and one column returns A1:A2. It does not delete the cells outside that new reference.

Why does Resize start in the wrong place?
It anchors at the top-left cell of the range expression it receives. Check whether the original reference is a multi-cell range or whether an Offset has moved the anchor before Resize is applied.

Does Resize insert rows or columns?
No. It only creates a reference of a specified size. Use a worksheet insertion method when the task is to add cells and shift existing content.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.