Hi. Below is a post from Tom Ogilvy that was posted back in 2003. The post
involved identifying which named range a given cell belonged to. So I took
the below snippets, created a named range called "test" in cells a1:c5. But
when I select b3, and run the below in the immediate window, I get b3 back
for the .address. For .name, I get an error. Am I doing something wrong?

? Range("A1").CurrentRegion.address
$A$1:$C$5
? Range("A1").CurrentRegion.name
=Sheet1!$A$1:$C$5
? Range("A1").CurrentRegion.name.name
MyRange

A1 to C5 needs to be filled as a block (the CurrentRegion), and not
connected to any other block.

HTH,
Bernie
MS Excel MVP

"Steph" wrote in message
...
Hi. Below is a post from Tom Ogilvy that was posted back in 2003. The
post
involved identifying which named range a given cell belonged to. So I
took
the below snippets, created a named range called "test" in cells a1:c5.
But
when I select b3, and run the below in the immediate window, I get b3 back
for the .address. For .name, I get an error. Am I doing something wrong?

? Range("A1").CurrentRegion.address
$A$1:$C$5
? Range("A1").CurrentRegion.name
=Sheet1!$A$1:$C$5
? Range("A1").CurrentRegion.name.name
MyRange

valid_rangereference.name.name only returns the name if the entire range
defined by the name is offered as the reference

If you selected a1:C5 and did

? selection.Name.Name

it would return Test.

but just selecting a cell within the range will return an error.

You would have to loop through the names collection to discover if a cell
was a subset of a named range.

if you had data in A1:C5 and at least a blank row and column separating that
range from other filled cells, then

?Range("A1").CurrentRegion.Name.Name

would return Test

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi. Below is a post from Tom Ogilvy that was posted back in 2003. The
post
involved identifying which named range a given cell belonged to. So I
took
the below snippets, created a named range called "test" in cells a1:c5.
But
when I select b3, and run the below in the immediate window, I get b3 back
for the .address. For .name, I get an error. Am I doing something wrong?

? Range("A1").CurrentRegion.address
$A$1:$C$5
? Range("A1").CurrentRegion.name
=Sheet1!$A$1:$C$5
? Range("A1").CurrentRegion.name.name
MyRange