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

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