This post will guide you how to get the active Cell address with a formula in Excel. How do I return address of active cell with a VBA Macro in Excel.

1. Get the Active Cell Address with Formula

If you want to return the address of an active cell in your current worksheet, you can use a formula based on the ADDRESS function, the ROW function and the COLUMN Function to achieve the result. The formula is shown as below:

=ADDRESS(ROW(),COLUMN())

You just need to select a cell and make it as active cell, then enter this formula into the formula bar, press Enter key on your keyboard. You would notice that the address of active cell is returned.

2. Get the Active Cell Address with VBA

You can also use an Excel VBA Macro to achieve the same result of returning the active cell address to a given cell. You just need to do the following steps:

#1 right click on the sheet tab in your worksheet, and select View Code from the popup menu list. And the Microsoft Visual Basic for Application window will appear. Or you can directly press Alt+ F11 to launch the VBA window.

#2 type the following code into the code window, then click Save button to save it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("B1").Value = ActiveCell.Address
End Sub

#3 close VBA window and back to your worksheet. You can try to click or select one cell, the cell address of active cell is shown in Cell B2.

3. Video: Get the Active Cell Address

This Excel video tutorial, where we’ll uncover the strategies for retrieving the active cell address in your spreadsheets through two distinct methods – a formula-based approach utilizing the ADDRESS function, and a hands-on VBA (Visual Basic for Applications) solution.

https://youtu.be/ejaqQFwuzOE

4. Related Functions

• Excel ADDRESS function
  The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
• Excel COLUMN function
  The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
• Excel ROW function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….