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


  To refer multiple cells from A1 to D1


  To refer entire row 3


  To refer entire column C


  To refer a named 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




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()


 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()



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()


End Sub


Sub demo_copy()   

Range(“A1:A3”).Copy  Range(“C3”)

End Sub


See below figure which depicts the above example using detailed method




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


© Snagoff. All right reserved.