Excel VBA Range Address (5 Suitable Examples)

In this article, we will demonstrate the significance of Excel VBA range address, its common usage, and how they can contribute to our day-to-day professional tasks.

Excel VBA Range Address is a vital concept for professionals and beginners in Excel automation. This powerful feature allows you to precisely target cells and ranges in your spreadsheets, making tasks like data analysis, formatting, and calculations a breeze.

Download Practice Workbook
You can download the workbook, where we have provided a practice section on the right side of each worksheet. Try it yourself.

Syntax of Excel VBA Range Address

In Excel VBA, the Range Address has the following properties:

Parameters
The Range.Address expression has the following parameters in Excel VBA.

How to Use VBA Range Address in Excel: 5 Suitable Examples

The significance of Range.Address in VBA is paramount, as it serves as a powerful tool for precise and flexible referencing of cell ranges. One of its best abilities is to provide precise references to cell ranges, making it highly valuable when dealing with complex data structures or large datasets. Moreover, it helps professionals to efficiently select, modify, and extract specific cells or ranges, optimizing data manipulation tasks.

We can simply use either the Debug.Print or MsgBox syntax to demonstrate the use of Range.Address property.

Here are some basic examples of how to use the VBA Range.Address in Excel:

1. Getting the Address of a Single Cell

Insert the following code into the module. In this VBA code, we are defining a Range object rng that refers to cell B5 on the Data worksheet. The address variable stores the absolute address of cell B5 as “$B$5“. The Debug.Print statement displays the address in the immediate 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 else, we can simply use the following code to get the cell reference of a single cell. Pressing Run will cause the MsgBox to open a window displaying the address.

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

2. Getting the Address of a Range with Multiple Cells

We can apply the same code in the module to obtain the address of a range with multiple cells, just like in the previous method.

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

Alternatively, we can simply use the following code with the help of MsgBox to display the range of multiple cells.

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

Finally, pressing the run button will open up the following window to show the range address in Excel.

3. Getting the Absolute Address of a Range

Another basic use of Range.Address expression is to find out the absolute reference of cells. Check out the following code and example to understand the process.

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

Once you press the Run button, it will open a window displaying the cell addresses of the cells mentioned in the specified range.

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

We can also use the following code in the module if we want a different reference style, such as 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

Upon running the code, a dialog box will appear in Excel, displaying the range address in the format R5C2:R14C6, instead of the usual format with absolute references and dollar signs. It uses the Row and Column Numbers to identify the range.

5. Getting the External Reference of a Range

To obtain the external reference information, such as the workbook or worksheet from which the data originates, we can utilize the following code. This feature proves invaluable when dealing with intricate workbooks that rely on data from various sources or when sharing data across different Excel files

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

As you can see in the image below, the code exhibits both the workbook name, VBA Range Address.xlsm, and sheet name, Data, from which the cell range was addressed.

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

If you want to know the row number or the column number from the range address, you can even do that with a few lines of code. Additionally, you can count total row or column numbers also. Take a look at the following code, where we demonstrate how to extract the first-row number from the range 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

Using the same code, we can extract the first column number from the range address.

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 Get VBA Cell Range Address Without $

Usually, the syntax of Range.Address results in an absolute reference with the “$” sign included in the output. Nonetheless, it is possible to obtain the range address without the dollar sign. Check the following code.

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

As evident from the image above, the code successfully returns the range address without any absolute reference, denoted by the “$” sign.

Things to Remember

• Syntax Accuracy: Pay close attention to the syntax when using VBA Range.Address. Ensure that you provide the correct arguments, such as RowAbsolute, ColumnAbsolute, External, and RelativeTo, to get the desired range reference.
• Absolute vs. Relative Reference: Setting RowAbsolute and ColumnAbsolute to TRUE will yield an absolute reference with the “$” sign while setting them to FALSE will result in a relative reference without “$”.
• Range Object: Remember the argument for VBA Range.Address is a Range object. Make sure to define the Range properly to avoid errors in the output

Frequently Asked Questions

Q1. What is the R1C1 address in Excel VBA?
The R1C1 address in Excel VBA 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 would refer to cell A1, R2C3 would refer to cell C2, and so on.

Q2. How to set the range to variable VBA?
To set a range as a variable in Excel VBA, you can use the Dim statement to declare a Range variable and then use 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 whether the input is in uppercase or lowercase.

Conclusion

In Excel VBA range address feature, helps professionals in many ways. Whether you’re a data analyst, financial professional, or simply a frequent Excel user, know how to utilize VBA Range.Address empowers you to work more effectively with data and achieve better insights.

Understanding the syntax and functionality of Range.Address provides you with a competitive advantage and helps you manipulate, extract, or modify data or interdependent variables.

If you like this article, check out Exceldemy for more relevant content.