Excel allows you to easily see the location of the currently selected cell by examining the contents of the Name Box, to the left of the Formula Bar. This is fine and good, but there are times when you would like to have the address of a cell actually in a cell. For instance, you may want cell A1 to contain the address of the currently selected cell. This means that if cell E4 were selected, then A1 would contain its address, or $E$4. If you then pressed the right-arrow key, then the contents of A1 would change to $F$4.
You can grab the address of the currently selected cell by using the CELL worksheet function, in this manner:
=CELL("Address")
You should note that this function doesn't result in the contents of the cell changing every time you move to a different cell. Instead, the function is updated only when the workbook is recalculated, either by changing something in the worksheet or by pressing F9.
If, instead, you need to have "real time" reporting of the selected cell, you'll need to resort to using a macro. Follow these steps:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Range("A1").Value = ActiveCell.Address End Sub
Now, as you move about this single worksheet, the contents of A1 should be constantly updated to reflect your location.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12400) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Displaying the Selected Cell's Address.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When creating macros, you often need to process numbers in various ways. VBA allows you to convert a numeric value to an ...
Discover MoreMacros are often used to process the data in a worksheet. If that data includes dates that need to be processed, you'll ...
Discover MoreStrings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-07-03 10:33:32
CJ
Yes, Micky, assuming you code that references the address property, but this isn't always true.
2017-07-03 03:32:03
Michael (Micky) Avidan
@Alex B,
No need to work so hard.
Just hover your cursor over the word "address" and you are set.
(see Figure 1 below)
(see Figure 1 below)
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
Figure 1.
2017-07-02 05:03:12
Alex B
If you want to know the selected cell address while a macro is paused, you might just want to type
? ActiveCell.Address
into the immediate window. (? = print result to the immediate window).
This will work too if you just want to know where the cursor is right now, setting up the "=Cell" formula at this point would change the selected cell.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments