Excel VBA Range Address – 5 Examples

This is an overview.

Download Practice Workbook
Download the workbook and practice.

Syntax of the Excel VBA Range Address

Parameters
The Range.Address expression has the following parameters:

Range.Address in VBA is used for a precise and flexible referencing of cell ranges.

Use either Debug.Print or MsgBox syntax to see the use of Range.Address property.

Example 1 – Getting the Address of a Single Cell

• Enter the following code into the module.

The code defines a Range object rng that refers to B5. The address variable stores the absolute address of  B5 as “$B$5“. The Debug.Print statement displays the address in the window.

Sub Exmple_1()
Dim rng As Range
Set rng = Worksheets("Data").Range("B5")
Dim address As String
address = rng.address
Debug.Print address ' Output: $B5
End Sub

• Or use the following code to get the cell reference of a single cell. Click Run to display the MsgBox with the address.

Sub Basic_1()
MsgBox Range("B5").address
End Sub

Example 2 – Getting the Address of a Range with Multiple Cells

• Use the same code in the module.

Sub Exmple_1()
Dim rng As Range
Set rng = Worksheets("Data").Range("B5:F14")
Dim address As String
address = rng.address
Debug.Print address ' Output: $B5$F14
End Sub

• Or use the following code with the help of the MsgBox to display the range of multiple cells.

Sub Basic_1()
MsgBox Range("B5:F14").address
End Sub

• Click run to see the output.

Example 3 – Getting the Absolute Address of a Range

• To find the absolute reference of cells, use this code:

Sub Basic_1()
Set rng = Range(Worksheets("data").Cells(5, 2), Worksheets("data").Cells(14, 6))
'Setting Range address
MsgBox rng.address
End Sub

• Click run to see the output.

Example 4 – Getting the Address of a Range with a Specific Reference Style (R1C1)

• Use the following code to get a different reference style, here R1C1.

Sub Basic_1()
Set rng = Range("B5:F14")
'Getting the range address as R1C1 reference style
MsgBox "The range address is " & rng.address(ReferenceStyle:=xlR1C1)
End Sub

• Click run to see the output.

Example 5 – Getting the External Reference of a Range

• To get the external reference, utilize the following code.

Sub Basic_1()
MsgBox Range("B5:F14").address(External:=True)
End Sub

The code shows both the workbook name, VBA Range Address.xlsm, and sheet name, Data.

How to View a Row or Column Number from a VBA Range Address in Excel

• Use the following code (here, to extract the first-row number in B5:F14).

Sub row_address()
Set rng = Range("B4:F15")
'View row number from range address
MsgBox "The First row number is: " _
& rng.Row
End Sub

• Use the same code to extract the first column number:

Sub row_address()
Set rng = Range("B4:F15")
'View row number from range address
MsgBox "The First Column number is: " _
& rng.Column
End Sub

How to a Get VBA Cell Range Address Without $

• Enter the following code.

Sub Basic_1()
MsgBox Range("B5:F14").address(RowAbsolute:=False, ColumnAbsolute:=False)
End Sub

Frequently Asked Questions

Q1. What is the R1C1 address in Excel VBA?
R1C1 is an alternative way to reference cells using row and column numbers. In R1C1 notation, R represents the row number, and C represents the column number. For example, R1C1 refers to A1, R2C3 refers to C2, and so on.

Q2. How to set the range to a variable in VBA?
Use the Dim statement to declare a Range variable and enter the Set keyword to assign the range to the variable. For example:

Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:B10")

Q3. Is VBA Range.Address case-sensitive?
No, VBA Range.Address is not case-sensitive. It will return the same address regardless of uppercase or lowercase.