Macro to Show Named Ranges Containing the Active Cell

ID: Q68504

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

The following Microsoft Excel macro finds all named ranges that contain the active cell on a worksheet and displays the names in a series of alert boxes. (Note: this is also true for version 5.0 macro sheets when you are using version 4.0 macros.)

MORE INFORMATION

Create the following macro:


A1:     find.names
A2:     =GET.DOCUMENT(1)
A3:     =FOR("count",1,COLUMNS(NAMES(A2)))
A4:     =INDEX(NAMES(A2),1,count)
A5:     =SUBSTITUTE(GET.NAME(A2&"!"&A4),"=","")
A6:     =IF(NOT(ISERROR(TEXTREF(A2&"!"&A4) ACTIVE.CELL())),ALERT("Cell
         is in : "&A4,2))
A7:     =NEXT()
A8:     =ALERT("No more names",3)
A9:     =RETURN()

Note: There is a space between "A4)" and "ACTIVE.CELL()" in cell A6. This space is the intersection operator.

Running this macro will generate an alert box displaying a named range that contains the active cell, or a message saying there are "No more names." Choosing OK will continue to display named ranges containing the cell until no more ranges are left.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 5.0

Keywords : kbcode kbmacro kbprg
Version : WINDOWS:2.x,3.0,4.0,5.0; :2.2,3.0
Platform : WINDOWS
Issue type :