Resize Method

This allows you to alter the size of a range

Range("A1").Resize(2,3) = C2 

expression.Resize(RowSize, ColumnSize)

RowSize - The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.
ColumnSize - The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.
Both of these arguments are optional and if omitted then the range stays the same.
This example resizes the selection on Sheet1 to extend it by one row and one column.

Worksheets("Sheet1").Activate 
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select


This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion 
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
    tbl.Columns.Count).Select

or

Dim inoofcolumns As Integer 
Dim lnoofrows as Long

inoofcolumns = ActiveCell.CurrentRegion.Columns.Count
ActiveCell.CurrentRegion.Offset(1,0).Resize(lnoofrows - 1, inoofcolumns).Select



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext