How do I return the name of a Range?

[**Archie**]

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.

 

[Chip Pearson]

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