Identifying named range from ActiveCell within the range

[fpperryaz]

Can someone tell me the syntax to use to get the Named Range address, from the Active Cell. I have selected a cell within a named range and need to identify the range for a macro. Thanks for any help.

Frank

 

[Tazguy37]

I don't quite understand. You want the address of the named range, or the address of the active cell?

For the active cell, you could use:

ActiveCell.Address

For a named range, use:

Range("myRange").Address

where myRange is the name of the range.

HTH

 

[Legacy 1363]

If you want to get the name and address of the named range that contains the selected cell :-

Sub Get_NamedRange_Address()
Dim n As Variant, rng As Range
If Selection.Cells.Count <> 1 Then Exit Sub
On Error GoTo e
For Each n In Application.Names
    Set rng = Range(Mid(n, 2))
    If Not rng Is Nothing Then
        If Not Intersect(rng, Selection) Is Nothing Then
            MsgBox "The selected cell is in " & n.Name & " (" & rng.Address(0, 0) & ")"
            Exit Sub
        End If
    End If
e:
Next n
MsgBox "There is no named range that contains the selected cell"
End Sub

 

[Joe Was]

This will goto each Named Range one at a time!

Sub allRangeNm()
For Each nm In ThisWorkbook.Names
If nm.Name <> "" Then
Range(nm).Select
MsgBox "Found one At: " & nm & vbLf & vbLf & "Named: " & nm.Name
End If
Next nm
End Sub

 

[fpperryaz]

re: Identify named range of Active cell

TazGuy: I know the address of the ActiveCell. I want the name of the range that contains it so that I can select the whole range for use. Thanks.

Mr Jones & Joe: I will test your code in the morning and report back. Thanks

Frank

 

[Legacy 1363]

Also here :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;213413