How do I return the name of a Range?
 

I have a group of cells that I have named "Test".

How can I return or retrieve the name of the range from one of the
cells (ActiveCell) in the range.

I have tried things like ActiveCell.Range.Name but this results in an
error.

How do I return the name of a Range?
 

You have to loop through the Names collection and find those names whose
RefersToRange property includes the ActiveCell. For example,

Dim Nm As Name
Dim ISect As Range
On Error Resume Next
For Each Nm In ThisWorkbook.Names
Set ISect = Nothing
Set ISect = Application.Intersect(ActiveCell, Nm.RefersToRange)
If Not ISect Is Nothing Then
Debug.Print "Name: " & Nm.Name & " contains the activecell."
End If
Next Nm



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"**Archie**" wrote in message
...
I have a group of cells that I have named "Test".

How can I return or retrieve the name of the range from one of the
cells (ActiveCell) in the range.

I have tried things like ActiveCell.Range.Name but this results in an
error.