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 this object in VBA in Excel.

Download Practice Workbook

5 Suitable Properties to Use 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.

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

1. VBA Range.Value Property in Excel

First of all, we’ll use the most common property of this object. 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:

⧪ VBA Code:

Sub Value()

MsgBox Range("B4").Value

End Sub

⧭ 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 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 following formula into cell E4 using VBA code.

To apply this SUM formula using VBA, The line of code will be:

⧪ VBA Code:

Sub Formula()

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

End Sub

⧭ Output: It’ll insert the SUM formula into cell E4 of the active worksheet.

⧭ 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 following formula into each cell of the range C4:C13.

3. VBA Range.Cells Property in Excel

Here Range.Cells is another important property of the 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:

The complete VBA code will be:

⧪ VBA Code:

Sub Cells()

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

End Sub

⧭ Output:

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

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

Similar Readings

• How to Use VBA Range Offset (11 Ways)
• Excel VBA Copy Range to Another Sheet (8 Easiest ways)
• Loop through a Range for Each Cell with Excel VBA (Ultimate Guide)

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:

⧪ 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.

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

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

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

⧭ 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:

• 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

• How to Count Text in Excel (7 Easy Tricks)
• End of a Range Using VBA in Excel (With Examples)
• Excel Subscript Out of Range Error in VBA (with 5 Solutions)
• VBA Range with Variable Row Number in Excel (4 Examples)
• How to Use the UsedRange Property of VBA in Excel (4 Ways)