Name of range containing ActiveCell?

[Ed]

Is there an easy way to get the name of the range containing the ActiveCell?
I have a macro that loops through all the names in the workbook and sees if
they intersect with the ActiveCell - but isn't there an easier way? I tried
ActiveCell.Range.Name, and getting the row and column references for
Cells(r,c).Range.Name, but nothing worked.

Ed

 

[Bob Phillips]

Ed,

I may be proved wrong, but I wouldn't have thought so. It could be in many
named ranges.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

Ed,

Sub test()
Dim rng As Range, str As String
Set rng = Selection 'or activecell etc
On Error Resume Next
str = rng.Name.Name
If Err.Number Then
str = "Unamed"
Err.Clear
End If
MsgBox rng.Address & vbCr & str
End Sub

Regards,
Peter T

 

[Tom Ogilvy]

If the named range is one cell and that corresponds to the activecell, then

activeCell.Name.Name

will work. But it raises an error if the active cell is not in a named
range or the named range includes more than one cell.

Otherwise, you need to loop through the names.

 

[Ed]

Hadn't thought of that one. You're right, of course, and that could
probably give a Message Box fits! Guess I'll stick with the long way (since
that's what I'm stuck with <g>).
Ed

 

[Ed]

Peter: Sorry, but it didn't work. I think it has something to do with the
restrictions mentioned by Tom - it doesn't seem to work if the range
contains more than one cell. So I'll use the loop macro I have.

Thanks for the input.
Ed

 

[Ed]

Thanks for the help, Tom. I have no clue as to why it would matter if the
range includes more than one cell - maybe the name then does not belong to
that cell? But it's enough (for now) to know the restrictions I'm operating
under.

Appreciate the boost.
Ed

 

[Peter T]

Ed,

I wonder why it doesn't work for you. For me it works with more than one
cell, a block of cells or even a multiple range, providing the range is
exactly a named range. If a multiple range it would need to be selected in
the same order as defined.

Regards,
Peter T

 

[Tom Ogilvy]

I suspect he means that it does not return the range name if the activecell
is located within a multicell named range. In other words, it works, but
not the way he wants it to work.

 

[Ed]

Tom and Peter: I have a named range which is five rows by 20 columns. I
selected one cell in that range and ran Peter's macro. The message box said
"Unamed". Wouldn't this mean the macro could not see the name of the range
the cell belonged to?

Ed

 

[Tom Ogilvy]

Yes, but that is not a mystery. So the code is working as Excel Works/Peter
designed it. As I said to Peter,
In other words, it works, but not the way he (Ed) wants it to work.

 

[Tom Ogilvy]

Another check you could do it you just want to know if it is in you named
range

if intersect(activecell,Range("MyRange")) is nothing then
Msgbox "Active cell is not in named range"
else
MsgBox "ActiveCell is in named Range"
End

This assumes the activecell and Myrange are at least on the same sheet.

 

[Peter T]

Just to add to Tom's excellent advice, if you (Ed) reference your full named
range of 5x20 cells the routine should return the Name. But, as I mentioned
before, "providing the range is exactly a named range". But no way
"directly" to return the name of some named range that some cell is
somewhere inside of.

Regards,
Peter T

PS Good guess earlier from Tom as to why it wasn't working for Ed, verging
on the psychic!

 

[Dave Peterson]

I find the easiest way to find out if a cell is part of a named range is to use
Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

(ok, that doesn't help if you need it in the middle of your code--but it's still
a very useful addin.)