How to Use the Range Object of VBA in Excel (5 Properties)

One of the most important and widely used objects that we use in Excel VBA is the Range object. In this article, I’ll show you how you can use all the properties of the Range object in VBA in Excel.


Download Practice Workbook


How to Use the Range Object in Excel VBA

Here we’ve got a worksheet with the Names and Salaries of some employees of a company called Sunflower Group.

Data Set to Use the Range Object in VBA in Excel

Today we’ll see various applications of the Range object of Visual Basic Application (VBA) on this data set.


1. VBA Range.Value Property in Excel

First of all, we’ll use the most common property of the Range object.

This is the Range.Value property.

The Range.Value property of VBA displays the value of a particular cell in Excel.

For example, to access the value in cell B4 of your worksheet, you have to use:

Range("B4").Value

VBA Code:

Sub Value()

MsgBox Range("B4").Value

End Sub

Value Property of the Range Object of VBA in Excel

Output:

It shows the value in cell B4 of the active worksheet (Frank Orwell in this example.)

Note: This property is only applicable when you use a single reference inside the Range object, like Range(“B4”).Value.

But if you use a range of cell references like Range(“B4:B13”).Value, it’ll show an error.

Read More: VBA to Set Range in Excel (7 Examples)


2. VBA Range.Formula Property in Excel

Next, we’ll see the Range.Formula property of VBA in Excel.

This property is used to insert any formula into a cell of the active worksheet through VBA.

You have to wrap the formula within an apostrophe (“”) before putting it into the code.

Let’s insert the formula =Sum(C4:C13) into cell E4.

The line of code will be:

Range("E4").Formula = "=Sum(C4:C13)"

VBA Code:

Sub Formula()

Range("E4").Formula = "=Sum(C4:C13)"

End Sub

VBA Code to Use the Range Object in VBA in Excel

Output: It’ll insert the formula =Sum(C4:C13) into cell E4 of the active worksheet.

Output to Use the Range Object in VBA in Excel

Note: This property is applicable for not only a single cell reference, but also a range of cell references inside the Range object.

For example, if you use Range(“E4:E13”).Formula = “=Sum($C$4:$C$13)”, it’ll insert the formula =Sum($C$4:$C$13) into each cell of the range C4:C13.


3. VBA Range.Cells Property in Excel

This is another important property of the Range object of VBA in Excel.

This property is used to access a particular cell within a range in Excel.

For example, let’s try to access the value of the 2nd row and 1st column of the range B4:C13.

We can use this line of code:

Range("B4:B13").Cells(2,1)

The complete VBA code will be:

VBA Code:

Sub Cells()

MsgBox Range("B4:B13").Cells(2,1)

End Sub

VBA Code to Use the Range Object in VBA in Excel

Output:

It’ll display the value from the 2nd row and 1st column of the range B4:B13, that’s from cell B5.

Output to Use the Range Object in VBA in Excel

Read More: VBA for Each Cell in Range in Excel (3 Methods)


Similar Readings


4. VBA Range.Cells.Item Property in Excel

Instead of the Range.Cells property of VBA, you can use the Range.Cells.Item property of VBA.

They are used for the same purpose, to access a specific cell within a range in an Excel worksheet.

To access the cell from the 2nd row and 1st column of the range B4:B13, use can use this line of code:

Range("B4:B13").Cells.Item(2, 1)

VBA Code:

Sub Item()

MsgBox Range("B4:B13").Cells.Item(2, 1)

End Sub

Output:

It’ll display the same output as above, the value from the 2nd row and 1st column of the range B4:B13 (B5).

Read More: How to Select Range Based on Cell Value VBA (7 Ways)


5. VBA Range.Rows and Range.Columns Properties in Excel

You can use the Range.Rows and the Range.Columns properties in VBA to access a whole row or a whole column of a range in VBA.

For example, to access the 2nd row of the range, B4:C13, you have to use:

Range("B4:C13").Rows(2)

Similarly, to access the 2nd column of the range B4:C13, you have to use:

Range("B4:C13").Columns(2)

Let’s try to set the values of all the cells of the 2nd column of the range B4:C13 to $40,000.

The VBA code will be:

VBA Code:

Sub Rows()

Range("B4:C13").Columns(2) = 40000

End Sub

VBA Code to Use the Range Object in VBA in Excel

Output:

It’ll set all the values of the 2nd column of the range B4:C13 (Column C, Salary) to $40,000.

Read More: VBA to Use Range Based on Column Number in Excel (4 Methods)


Things to Remember

  • The Range object acts on the active worksheet on your workbook by default. But if you want it to act on a particular worksheet every time, no matter what the active worksheet is, you can fix it in the code by putting the name of the worksheet before the Range object.
  • For example, to act on the worksheet “Sheet1” every time, you can fix it in your code in this way:
Worksheets("Sheet1").Range("B4:C13")
  • Instead of specifying the range in the code, you can make it a bit flexible by using the Selection object in VBA.
  • Each time you select a different range of cells in your worksheet, the Selection object holds that range. For example, if you select the range D4:F13 on your worksheet, Selection.Cells(2,3) will mean cell F5.

Conclusion

These, in short, are the most important and widely used properties of the Range object that we use the most while working in VBA. Obviously, there are a few more properties and a bunch of methods associated with the object. We’ll learn those in the upcoming articles. Up till now, do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo