Excel VBA Resize Range
In this Article
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 for the Resize property is:
Range("A1").Resize (RowSize, ColumnSize)
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.
Or, more commonly you’ll assign the resized range to a variable:
' Resize range to the desired size and assign to a variable Set newRng = ActiveSheet.Range("A1").Resize(10, 5)
Resize Number Of Rows Only
Following example changes the number of rows only:
' Change Row Size only, New Range will be $A$1:$A$10 Set newRng = rng.Resize(10)
Resize Number Of Columns Only
Following example changes the number of columns only:
' Change Column Size only, new Range will be $A$1:$E$1 Set newRng = rng.Resize(, 5)
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.
Sub SelectTableData() ' **IMPORTANT** ' Click on any cell of the table before running the macro ' Move down one row by using Offset and then reduce range size by one row Set tbl = ActiveCell.CurrentRegion.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count) ' Data is selected excluding header row tbl.Address.Select End Sub
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:
Sub WriteArray() ' Read the data to an array data = Range("A1:E10").Value ' Resize the output range and write the array Worksheets("Output").Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data End Sub
Written by: Vinamra Chandra