Call Names

I have noticed that when I test to see if a cell has a
named range i.e. if the name was based on Sheet$A$1 and I
use sName = rng.Name.Name I would pass the name to the
variable sName

However, this doesn't always pick it up if the named
range is say Sheet$A$1:$A$1.


Why is this?

I'm not sure you're gonna get a good answer to your "why" question--except for
that's the way excel works.

But why would you choose to define a name so that it looks like it might be a
multiple cell range?

(There's a better chance I'll get an answer from you, than you getting an answer
from MS!)

ExcelMonkey wrote:
>
> I have noticed that when I test to see if a cell has a
> named range i.e. if the name was based on Sheet$A$1 and I
> use sName = rng.Name.Name I would pass the name to the
> variable sName
>
> However, this doesn't always pick it up if the named
> range is say Sheet$A$1:$A$1.
>
> Why is this?

--

Dave Peterson

Actually I had a named range which was originally
$A$1:$A$2 which I used to feed data validation list.
Then I decided to get rid of the A2 piece. However I did
it directly within the Name dialogue box. I just simply
went in an edited $A$1:$A$2 to $A$1:$A$1 out of
laziness. When I ran my cell audit routine looking for
named ranges, I expected to see that range as it was only
attributed to 1 cell. However, the routine did not pick
it up. This caught my attention and prompted me to think
about ways in which I may want to error handle this
routine going forward assuming there might be others out
there who are as lazy as me!

Thanks


>-----Original Message-----
>I'm not sure you're gonna get a good answer to
your "why" question--except for
>that's the way excel works.
>
>But why would you choose to define a name so that it
looks like it might be a
>multiple cell range?
>
>(There's a better chance I'll get an answer from you,
than you getting an answer
>from MS!)
>
>ExcelMonkey wrote:
>>
>> I have noticed that when I test to see if a cell has a
>> named range i.e. if the name was based on Sheet$A$1
and I
>> use sName = rng.Name.Name I would pass the name to the
>> variable sName
>>
>> However, this doesn't always pick it up if the named
>> range is say Sheet$A$1:$A$1.
>>
>> Why is this?
>
>--
>
>Dave Peterson
>.
>

Ahhh.

I thought you were doing extra work by putting that ":$a$1" at the end <vbg>.

I've never seen anyone do this (well, until today).

If I were you, I'd get a copy of 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

You can play around with something like this:

Option Explicit
Sub testme()

Dim myName As Name
Dim TestRng As Range

For Each myName In ActiveWorkbook.Names
Set TestRng = Nothing
On Error Resume Next
Set TestRng = myName.RefersToRange
On Error GoTo 0
If TestRng Is Nothing Then
'do nothing
Else
If TestRng.Cells.Count = 1 Then
TestRng.Name = myName.Name
End If
End If
Next myName

End Sub

But don't use this if you have dynamic ranges in your names.

A name that refers to:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
Could get screwed up really badly.





ExcelMonkey wrote:
>
> Actually I had a named range which was originally
> $A$1:$A$2 which I used to feed data validation list.
> Then I decided to get rid of the A2 piece. However I did
> it directly within the Name dialogue box. I just simply
> went in an edited $A$1:$A$2 to $A$1:$A$1 out of
> laziness. When I ran my cell audit routine looking for
> named ranges, I expected to see that range as it was only
> attributed to 1 cell. However, the routine did not pick
> it up. This caught my attention and prompted me to think
> about ways in which I may want to error handle this
> routine going forward assuming there might be others out
> there who are as lazy as me!
>
> Thanks
>
> >-----Original Message-----
> >I'm not sure you're gonna get a good answer to
> your "why" question--except for
> >that's the way excel works.
> >
> >But why would you choose to define a name so that it
> looks like it might be a
> >multiple cell range?
> >
> >(There's a better chance I'll get an answer from you,
> than you getting an answer
> >from MS!)
> >
> >ExcelMonkey wrote:
> >>
> >> I have noticed that when I test to see if a cell has a
> >> named range i.e. if the name was based on Sheet$A$1
> and I
> >> use sName = rng.Name.Name I would pass the name to the
> >> variable sName
> >>
> >> However, this doesn't always pick it up if the named
> >> range is say Sheet$A$1:$A$1.
> >>
> >> Why is this?
> >
> >--
> >
> >Dave Peterson
> >.
> >

--

Dave Peterson