Excel VBA Resize: Change a Range’s Size with VBA Code

In Excel VBA, use the Resize property to refer to a range with a different number of rows or columns: it keeps the original range’s top-left cell as its anchor. For example, starting from A1, resizing to three rows and two columns refers to A1:B3; it does not insert cells or change their physical dimensions.

What Excel VBA Resize means

Resize is a property of a Range object. It returns another Range object whose upper-left cell is the same as the original range’s upper-left cell, but whose height or width can differ. This makes it useful when the size of a data area depends on how many records are present.

The distinction between referring to a range and changing the worksheet is important. A resized range is a reference your code can read, format, copy, or use with another method. Calling Resize by itself does not insert or delete rows, move existing cells, expand column widths, or alter row heights. The rest of your VBA statement determines what happens to the range.

For instance, a range variable can represent a larger area after you assign it a resized reference. You could then apply formatting to that area or read its values. The original cells remain in place unless your code explicitly performs an operation that changes the sheet.

How the Resize arguments work

The method accepts a row count and a column count. The row count sets the returned range’s height, and the column count sets its width. These are counts, not ending row or column numbers. A count of three rows means three rows beginning at the anchor cell, not a range ending on worksheet row three.

Both arguments are optional. If you supply only one, the dimension you omit keeps its original size. The examples below show the common patterns; each one is a complete procedure with its variables declared.

Sub ShowResizePatterns()
    Dim anchor As Range
    Dim widerRange As Range
    Dim tallerRange As Range

    Set anchor = ThisWorkbook.Worksheets("Data").Range("B2:D4")

    ' Keep three rows and return two columns, starting at B2.
    Set widerRange = anchor.Resize(RowSize:=3, ColumnSize:=2)

    ' Keep the original three columns and return five rows, starting at B2.
    Set tallerRange = anchor.Resize(RowSize:=5)

    MsgBox widerRange.Address & vbCrLf & tallerRange.Address
End Sub

In that example, the original range is B2:D4. The first result is B2:C4: it retains three rows but has two columns. The second result is B2:D6: it retains three columns and has five rows. Because both results start at B2, neither operation shifts the anchor down or to the right.

Resize a known range

For a fixed-size area, start with a single cell and specify the desired height and width. If the starting cell is A1 and you need a block four rows tall and three columns wide, the returned range is A1:C4. This pattern is convenient for reading or formatting a predictable block without listing every cell address separately.

You can also start with a multi-cell range. In that case, the top-left cell anchors the result, while the requested counts determine its dimensions. For example, resizing B2:D4 to two rows and one column returns B2:B3. It does not shrink inward from every edge of the original range, and it does not center the smaller range inside it.

Use a range variable when you will refer to the result more than once. It makes code easier to read and reduces the chance of accidentally applying a later operation to a different selection. In most procedures, qualifying the starting range with a worksheet, as in the example, is also safer than relying on whichever sheet happens to be active.

Build a range from the last data row

A common reason to use Resize is to work with a list whose length changes. The procedure below finds the last non-empty cell in column A, then creates a range from A1 through the corresponding row in column D. It assumes row 1 contains headers and that column A is filled for every data record.

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

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

    ' Stop if the sheet has a header but no records.
    If lastRow < 2 Then
        MsgBox "No data rows were found."
        Exit Sub
    End If

    ' Include the header and four columns, from A through D.
    Set dataRange = ws.Range("A1").Resize(RowSize:=lastRow, ColumnSize:=4)

    ' Format the header and outline the full data area.
    ws.Range("A1").Resize(ColumnSize:=4).Font.Bold = True
    dataRange.BorderAround LineStyle:=xlContinuous
End Sub

If the last record is on row 12, the macro makes dataRange refer to A1:D12: 12 rows and four columns. The check for fewer than two rows prevents the procedure from treating a header-only sheet as though it contained records. Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z.

The method used to find the final row depends on your data. If column A has gaps near the bottom, the last non-empty cell in that column may not match the final record in another column. Choose a column that is reliably populated, or use a more suitable rule for the worksheet. Also consider whether cells containing formulas that display empty strings should count as part of your data.

Choosing row and column counts

Work out the dimensions before calling Resize. If a range should include one header row plus a variable number of records, its row count is the header plus those records. If a block spans columns A through D, its width is four, regardless of the row where it starts. This count-based way of thinking helps avoid off-by-one mistakes.

Include a header: count the header as a row if the resized range begins on the header cell.
Use the right width: count the columns in the data area, not the worksheet’s ending column number.
Check empty data: prevent a calculated row count of zero or a range that accidentally contains only headers.
Keep the anchor in mind: the result always begins at the original range’s top-left cell.

Counts must describe a valid, positive-sized range. A zero or negative row or column count is not a way to delete that dimension; it can cause a run-time error. A range that would extend beyond the worksheet’s available rows or columns is also invalid. Validate calculated sizes when they come from user input or from potentially unreliable data.

Resize a normal range or an Excel table?

Worksheet ranges and Excel tables are related, but they are not the same object. A regular Range can be resized to create a reference to a chosen rectangle of cells. A table, sometimes called a ListObject in VBA, has its own structure, including a header row and optional total row. Calling Resize on a Range does not, by itself, change a table’s defined boundaries.

If your goal is to change the cells belonging to a table, use the table’s table-specific resizing method rather than assuming that a resized Range variable has expanded the table. Table boundaries affect features such as structured references and automatic table behavior. If you only need to format or inspect a rectangular area near a table, a Range reference may be sufficient; if you need to change which rows are part of the table, work with the table object deliberately.

Resize compared with Offset and selection

Resize changes the dimensions of the returned reference while keeping its top-left cell fixed. Offset, by contrast, moves a reference by a number of rows or columns; it keeps the original dimensions unless you combine it with another operation. They solve different problems: use resizing to change range height or width, and use an offset when the target starts somewhere else.

It is also usually clearer to assign the result to a Range variable and use that variable directly than to select cells and then act on the selection. Selection-based code depends on the active worksheet and current selection, which can change unexpectedly. A fully qualified reference, such as one that begins with a worksheet variable, makes the intended target explicit and works more reliably when other sheets are active.

Common problems and how to avoid them

Unexpected starting cell: the anchor is the original range’s top-left cell, not necessarily the cell you had in mind. Check the starting range before resizing.
Wrong number of rows: row counts start at the anchor and include that row. If your data ends on worksheet row 12 and starts at row 1, the height is 12, not 11.
Data is missed: finding the last row in a column that contains blanks can undercount records. Pick a consistently populated column or use a different detection strategy.
Nothing appears to happen: resizing only returns a reference. Follow it with an action, such as assigning the result to a variable or applying formatting.
Run-time error: check for zero or negative counts, invalid worksheet references, and a requested range that would extend beyond the sheet.
Wrong workbook or sheet: unqualified references can point to the active sheet or workbook. Qualify the worksheet and workbook when the code must target a specific location.

When troubleshooting, inspect the calculated values before using them. You can temporarily show the resulting address in a message box or use the VBA editor’s debugging tools to step through the procedure. Confirm that the address, row count, and column count match the intended data area before applying an operation that changes formatting or content.

Running VBA in Microsoft 365

On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic on the ribbon. In the editor, choose Insert > Module, place a procedure in the module, and run it from the editor or Excel’s macro tools. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

Excel for Mac supports VBA, though some features and operating-system interactions differ from Windows. The Developer tab’s Visual Basic command is available in desktop Excel for Mac; the keyboard shortcut can depend on the keyboard and function-key settings, commonly requiring Option+F11 or Fn+Option+F11. Excel for the web does not run VBA macros, so open the workbook in desktop Excel to use them. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), and only enable macros from sources you trust.

Frequently Asked Questions

Does VBA Resize insert rows or columns?
No. It returns a reference to a range with the specified dimensions. To insert worksheet rows or columns, use a separate insertion operation; resizing a reference alone does not move cells.

Does Resize change the original range variable?
No. The property returns a Range object. Assign that result to a variable if you want to keep and use the new reference; the original range’s cells and dimensions are not modified by the call itself.

Can I change only the number of columns?
Yes. Supply a column count and omit the row count, so the returned range keeps the original height. The new reference still starts at the original range’s top-left cell.

Why does Resize return the wrong cells?
Most often, the anchor or the counts are wrong. The result begins at the starting range’s top-left cell, and the arguments specify total height and width—not ending row and column numbers. Check both before using the range.

Can I use VBA Resize in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run the procedure; the precise VBA capabilities can vary between those desktop platforms.

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.