Range Object in VBA
Range object is used to access the range of cells of Microsoft Office Excel in VBA. In this article we discuss about Range object and its usage.
Open Microsoft Office Excel 2013 => select DEVELOPER tab => add Command Button and add below code
Private Sub CommandButton1_Click()
Range("A1", "C11").Value = "This is from Range Object"
End Sub
Above code fills the cells from A1 to C11 with “This is from Range Object” when we click on button.
Range object is used to select the specified cells through the Select property of Cells as shown below.
Private Sub CommandButton1_Click()
Range("A1", "C11").Cells.Select
End Sub
The above code selects the cells from A1 to C11.
We can use Offset property to perform various operations on selected Range. Below code selects the particular cell from selected Range.
Private Sub CommandButton1_Click()
Range("A1").Offset(2, 5).Select
End Sub
Use Resize property to re size the selected Range. Below code re sizes the cell A1 to Range A1:C11.
Private Sub CommandButton1_Click()
Range("A1").Resize(11, 3).Select
End Sub
To access specific worksheet Range use Worksheets as shown below.
Private Sub CommandButton1_Click()
Worksheets("Sheet2").Range("A1", "C11").Select
End Sub
Above code selects the Range from cell A1 to Cell C11 of worksheet Sheet2.
To access range of outside workbook worksheet use Workbooks as shown below.
Private Sub CommandButton1_Click()
Workbooks("Products.xlsx").Worksheets("Sheet1").Range("A1", "C11").Select
End Sub
Above code access the worksheet sheet1 of workbook Products.xlsx and selects the Range from Cell A1 to Cell C11.
