Monday, 20 March 2017

What is the difference between Range.Resize and Range.Offset in VBA

Range.Resize Vs Range.Offset in VBA
Range.Resize Method is used to resize the range of cells from the Active cell Range.The Resize property enables you to change the size of a range based off the location of the active cell.
Example :
Sub Rng_Resize()

Range("C3").Resize(3, 3).Select

End Sub

This will select a range of 3 Rows and 3 Columns including the Active Cell range Row and Column, as follows

Notes :
To resize only Columns, you can skip the rows size as follows :
Range("C3").Resize( , 3).Select

To resize only Rows, you can directly specify the rows size as follows :

The range.resize will not allow the Zero as size ; it will throw an error for below methods
Range("C3").Resize(0,3).Select             ----------- Error
Range("C3").Resize(3,0).Select             ----------- Error

Range.Offset Method is used to change the range of Active cell .The Offset property enables you to change the Active cell range to a new range based on offset of rows and columns from the location of the active cell.

Sub Rng_Offset()

Range("C3").Offset(3, 3).Select

End Sub

Notes :
To offset only Columns, you can mae the rows to zero as follows :
Range("C3").Resize( 0, 3).Select

To offset only Rows, you can mae the columns to zero as follows :


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts