find range name for active cell
 

I need to determine the name of the range for the active cell on a worksheet.

find range name for active cell
 

if the name refers to that single cell alone

msgbox activecell.name.name

if it could be part of a larger named range

for each nm in ThisWorkbook.Names
set rng = Nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
if not rng is nothing then
if rng.Parent.name = activeCell.parent.Name then
set rng1 = Intersect(rng,ActiveCell)
if not rng1 is nothing them
msgbox nm.Name
exit for
end if
end if
End if
Next

--
Regards,
Tom Ogilvy

"Len" wrote in message
...
I need to determine the name of the range for the active cell on a
worksheet.

find range name for active cell
 

Now that I have the name, how do I refer to that range? For example, if I
want to clear the range.

"Tom Ogilvy" wrote:

if the name refers to that single cell alone

msgbox activecell.name.name

if it could be part of a larger named range

for each nm in ThisWorkbook.Names
set rng = Nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
if not rng is nothing then
if rng.Parent.name = activeCell.parent.Name then
set rng1 = Intersect(rng,ActiveCell)
if not rng1 is nothing them
msgbox nm.Name
exit for
end if
end if
End if
Next

--
Regards,
Tom Ogilvy

"Len" wrote in message
...
I need to determine the name of the range for the active cell on a
worksheet.