VBA MODULE 7
VBA Excel Range
A Range Object can be a single cell or group of cells (may be a complete row or column of cells) on a worksheet and to perform different operations on these objects it has several properties and methods. This range object is like a lifeline for VBA as it is used most of the time while automating any task in VBA and that’s why to refer any data from the sheet while writing VBA code, you need Range Object.
Some examples to show the representation of Range Object without using any property and method:-
|
To refer a single cell A1 |
Range(“A1”) |
|
To refer multiple cells from A1 to D1 |
Range(“A1:D1”) |
|
To refer entire row 3 |
Range(“3:3”) |
|
To refer entire column C |
Range(“C:C”) |
|
To refer a named range itemlist |
Range(“itemlist”) |
You might have observed that all the range addresses are within the double quotes, this is the syntax which we have to follow.
Let’s talk about Methods and Properties of Range Object
Methods
A method is an action which is performed on the objects.
Range object has several methods but not all methods are very useful. So we will focus on the methods which are used commonly and necessary to understand
Select Method
Select method is used to select a range (a cell or group of cells).
Example for selecting a range from A1 to D4 on the current sheet which is open on your system
|
Sub demo_range() Range("A1:D4").Select End Sub |
See below figure which depicts the above example showing the excel sheet with the selected area and Vba code in the Vba editor
To select cells on a different worksheet, you have to activate that sheet first. For example, the following code lines select cell A1 on the second worksheet.
|
Sub demo_range() Worksheets(2).Activate Worksheets(2).Range("A1").Select End Sub |
Copy and Paste Method
The Copy method is used to copy a range and Paste method to paste it somewhere else on the worksheet.
|
Detailed Method |
Short Method |
|
Sub demo_copy() Range("A1:A3").Select End Sub
|
Sub demo_copy() Range(“A1:A3”).Copy Range(“C3”) End Sub |
See below figure which depicts the above example using detailed method
Properties
Range object has several properties. They describe the characteristics of object like its color, size, font, border etc.
The Value Property
The Value Property represents the value contained in the cell
|
Sub demo_property() Range(“A1”).Value = 2 End Sub |
The Font Property
Font property is used to change the font format of a range object
|
Sub demo_property() Range(“A1”).Font.Bold = True End Sub |