VBA Excel Get Range Address: Examples and Common Uses

VBA & macros · Updated November 22, 2026

In Excel VBA, get a range’s address with its Address property: for example, Range("B2:D5").Address returns $B$2:$D$5 by default. Set the property’s optional arguments to choose absolute or relative references, A1 or R1C1 notation, and whether to include the worksheet and workbook.

What the Address property returns

Address returns a string describing the cells represented by a Range object. It reports the location of the range; it does not return the values stored in those cells. A single-cell range produces one cell reference, while a multi-cell range produces a reference spanning its first and last cells. For example, the default address for B2:D5 includes dollar signs before both the column letters and row numbers.

The method’s full argument order is Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo). All five arguments are optional. The defaults produce an absolute A1-style address without a workbook or worksheet prefix. You can pass arguments by position, but named arguments make the intent easier to read and reduce mistakes when you omit an argument in the middle.

Remember that a returned address is text. You can show it in a message box, write it to a cell, concatenate it into a larger string, or use it when constructing another reference. Getting an address is different from reading a value: use a range’s Value or Value2 property when you need its contents.

Choose absolute or relative references

The first two arguments control whether the row and column parts are absolute. By default, both are absolute, which is why the usual result contains dollar signs. Set both arguments to False to remove them in A1 notation. Set only one to False to create a mixed reference.

  • Address or Address(RowAbsolute:=True, ColumnAbsolute:=True) returns $B$2 for a one-cell range at B2.
  • Address(RowAbsolute:=False, ColumnAbsolute:=False) returns B2.
  • Address(RowAbsolute:=True, ColumnAbsolute:=False) returns B$2.
  • Address(RowAbsolute:=False, ColumnAbsolute:=True) returns $B2.

These are the same absolute and mixed-reference conventions used in Excel formulas. They are useful when VBA builds a formula or when you need to display a location in a particular form. If the address is only for identification or logging, the default absolute form is often the clearest because it does not depend on a separate starting cell.

Get an address in a VBA procedure

To use the property in a macro, first identify the Range object you want. Qualifying it with a workbook and worksheet avoids relying on whichever sheet happens to be active. The following procedure shows a specific range’s default address. Replace the worksheet name or cell reference to match your workbook.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when the macro only appears to display information; a later edit to the example could change cells or other workbook content.

Sub ShowRangeAddress()
    Dim target As Range

    ' Identify the range on the intended worksheet.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2:D5")

    ' Display its default absolute A1 address.
    MsgBox target.Address
End Sub

Here, ThisWorkbook means the workbook containing the VBA code, not necessarily the workbook currently selected in Excel. If the macro is stored in a different workbook, such as the Personal Macro Workbook, use an explicit workbook reference appropriate to the task. The worksheet name must also match an actual sheet tab name.

To place the result in a worksheet cell, assign the string returned by Address to that cell’s value. For example, with a target range already assigned to a Range variable, VBA can put its address in a separate output cell. Keep the source range and output cell distinct if you do not want to overwrite the data whose location you are reporting.

Use A1 and R1C1 reference styles

The ReferenceStyle argument determines how row and column positions are written. The default is xlA1, which uses column letters and row numbers, as in $C$7. Use xlR1C1 for row-and-column notation. With absolute settings, the same cell can be represented in R1C1 form as R7C3.

R1C1 notation can also express a location relative to another cell. When building a relative R1C1 address, provide RelativeTo explicitly so the reference point is clear. For example, C3 is two rows down and two columns to the right of A1, so its relative address from A1 is R[2]C[2].

Sub ShowRelativeR1C1Address()
    Dim target As Range
    Dim origin As Range

    ' Set the cell and the reference point on the same worksheet.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("C3")
    Set origin = ThisWorkbook.Worksheets("Sheet1").Range("A1")

    ' Return an R1C1 address relative to A1.
    MsgBox target.Address(RowAbsolute:=False, _
                          ColumnAbsolute:=False, _
                          ReferenceStyle:=xlR1C1, _
                          RelativeTo:=origin)
End Sub

In this example the relative offsets are positive because C3 is below and to the right of A1. If the target were above or to the left of the origin, the relative notation would use negative offsets. The RelativeTo argument is especially helpful in code that generates formulas or compares positions, because it makes the base cell unambiguous.

Include the worksheet or workbook name

Use the External argument when the result needs to identify its source sheet or workbook as well as the cell location. Set External:=True to request an external-style address. This is useful when a string will be passed between procedures or written to a log that includes references from more than one sheet or workbook.

The exact displayed prefix depends on the workbook and worksheet names. Names containing spaces or punctuation may be enclosed in apostrophes, and a workbook name may be included. For that reason, avoid assuming that every returned string will have the same number of characters or the same prefix. If you only need the address on a known worksheet, qualify the Range object itself in VBA and leave External at its default.

For instance, code can request an external-style address from a worksheet-qualified range as follows. This example demonstrates the property call; it does not change any cells.

Sub ShowExternalAddress()
    Dim target As Range

    ' Identify the range before requesting its full address.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2:D5")
    MsgBox target.Address(External:=True)
End Sub

Address a selection, cell, or multi-area range

The property works on any Range object, so the address can describe an active cell, a selected range, a named range resolved to cells, or a range stored in a variable. A common source of bugs is using an unqualified expression such as Range("A1") in code that may run while another worksheet is active. In general, attach the range to a specific worksheet when the intended location is known.

For a selection-dependent task, first verify that the user has selected cells before treating Selection as a Range. A user can select a chart, shape, or other object, and code that assumes every selection is a range may fail. If the macro is meant to work on one known cell or block, referencing that range directly is more reliable than relying on the current selection.

A range can also contain multiple separate areas, such as a selection created by Ctrl-clicking more than one block. Its address represents those areas as a multi-area reference. If your task requires processing each block independently, inspect the range’s Areas collection and handle each area separately rather than assuming the result is one continuous rectangle. Address punctuation can be affected by Excel’s list separator settings, so avoid using a returned multi-area string as a dependable way to split the areas.

Address versus AddressLocal and other alternatives

Use Address for the standard VBA address representation. Excel also provides AddressLocal, which returns the address using the language and regional conventions of the user’s Excel installation. This can matter when presenting an address to a local user or when working with localized formula syntax. If the string will be consumed by code that expects standard VBA reference notation, the ordinary Address property is generally the more appropriate choice.

Do not confuse an address with a range’s name or its value. A named range can refer to cells, but its name is not necessarily the same as the cells’ address. Similarly, Range("B2").Value reads the cell’s content, while Range("B2").Address returns its location. Choose based on what the next step needs: a cell reference, a displayed label, a defined name, or the data itself.

Common problems and practical checks

If the returned address does not look as expected, check the Range object and the arguments before changing the string afterward. Most surprises come from a default absolute reference, a different active worksheet than expected, or a mismatch between the requested reference style and the format being anticipated.

  • Unexpected dollar signs: They are normal defaults. Set the row and column absolute arguments to False if you need a relative A1 address.
  • Wrong sheet: Qualify the range with the intended workbook and worksheet instead of relying on an unqualified Range reference.
  • Unexpected R1C1 result: Confirm that ReferenceStyle:=xlR1C1 is passed and provide RelativeTo when requesting a relative address.
  • Macro error on a selection: Confirm that the selected object is a range, or use a known worksheet range instead of Selection.
  • Address text changes across workbooks: If External is True, workbook and sheet names contribute to the output. Do not hard-code assumptions about the prefix.

It is also worth distinguishing a valid address string from a valid range in the current context. A string may refer to a different sheet or workbook, or to a location that is no longer the one your code intended. When possible, keep and pass Range objects within your procedure, and use the address chiefly where text is actually required, such as for display, logging, or constructing a reference.

Frequently Asked Questions

How do I get the address of the active cell in VBA?
Use the active cell’s Address property, such as ActiveCell.Address. For code that must work on a particular worksheet regardless of what the user has selected, reference that worksheet’s range instead.

How do I remove the dollar signs from a VBA address?
Set both absolute arguments to False: target.Address(RowAbsolute:=False, ColumnAbsolute:=False). For a B2 target, this returns B2 rather than $B$2.

How do I get the full address including the sheet name?
Request an external-style address with target.Address(External:=True). The result can include the worksheet and workbook, and its exact text depends on their names.

Does Address return a cell’s value?
No. It returns a string describing the range’s location. Read the cell’s Value or Value2 property when you need the contents.

Can Address return an R1C1 reference?
Yes. Set ReferenceStyle:=xlR1C1. For a relative R1C1 address, also provide the reference point with RelativeTo so the offsets are based on the cell you intend.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.