Excel VBA Resize Range

Associated Files Download Links

Resize Range Using VBA

This tutorial will demonstrate how to use the Resize Property of the Range Object to change to return a new range resized from the original Range object.

Syntax

Syntax for the Resize property is:

Where Range(“A1”) is your starting range.

RowSize and ColumnSize must be greater than zero. Each input is optional (ex. you can omit RowSize to only change the number of columns, or vice-versa).

Resize Number Of Rows And Columns

Following example expands the range with one cell A1 to range A1:D10 by increasing row count to 10 and columns count to 5.

vba resize range

Or, more commonly you’ll assign the resized range to a variable:

Resize Number Of Rows Only

Following example changes the number of rows only:

Resize Number Of Columns Only

Following example changes the number of columns only:

Resize Table Range To Exclude Header

If you have a table on active sheet with a header row, the code will first select the entire table then move down one row to exclude the header using Range.Offset method. It will then use the Range.Resize property to reduce the size by one row.

Write 2-D Array To Range

Another common usage is to write a 2 dimensional array to a sheet. Since the range to be written should match the size of the array, which is normally not known in advance, Resize method is used to set the output range

Example below will read the data in the range A1:E10 in active sheet to an array and write the array out to ‘Output’ sheet starting from cell A1:

Written by: Vinamra Chandra