Excel VBA Range Object

Range Object

The Range object refers to the selection of cell or cells on a worksheet. It is the most important and a significant object in MS Excel VBA. In this tutorial, you will observer the properties and methods of the Range object.

Place a Command Button on a Worksheet and place the following code under the click event.

Range(“A2”).Value = 10

Now, click the button and the result would be similar to the figure given below.

Try another example using the Code:

Range(“A1:A5”).Value = 10

Desired result would be:

 

Cells

Instead of using a Range function, Cells function can also be used. It is particularly useful and effective when you want to loop through ranges.

For example

Cells(3, 2).Value = 15

Result:

Explanation: MS Excel VBA enters the value 15 into the cell at the intersection of row 3 and column 2.

Let’s try a combination of Range and Cells functions.

Range(Cells(2, 2), Cells(3, 3)).Value = 10

Result:

Declare a Range Object

A Range Object can also be declared using a Dim Keyword and its value can be set using a Set Keyword. To get a better understanding of this concept, follow the steps below:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MyRange.Value = 25

Result:

Select

Select is an important method associated with Range object. This method simply selects a defined range. To get a better understanding of the concept, follow the code below:

Code:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MyRange.Value = 25

MyRange.Select

Result:

Rows

The Rows property allows the access to a specific row of a given range.

Code:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MyRange.Rows(2).Select

Result:

Columns

Similar to Rows, Columns property allow access to a particular column of a given range.

Code:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MyRange.Column(2).Select

Result:

Copy/Paste

The Copy and Paste method is used to copy a defined range and to paste it to some other location on the worksheet.

Code:

Range(“A1:C1”).Select
Selection.Copy
Range(“B3”).Select
ActiveSheet.Paste

Result:

Although the above code works in MS Excel VBA but it is much better to use the code below which gives exactly the same results.

Range(“A1:C1”).Value = Range(“B3:D3”).Value

Clear

ClearContents method is used to clear the content of an Excel range

Range(“A1:D4”).ClearContents

or

Range(“A1:D4”).Value = “”

Likewise, if you want to only clear the formatting then you may use ClearFormats method rather than ClearContents method.

Count

Count property is used to count the number of cells, rows and columns in a given range. To have a better understanding of the concept, follow the code below:

Code:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MsgBox MyRange.Count

Result:

Code:

Dim MyRange As Range
Set MyRange = Range(“A1:D4”)

MsgBox MyRange.Rows.Count

Result: