Monday, March 20, 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 :
Range("C3").Resize(3).Select

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 :
Range("C3").Resize(3,0).Select

Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog