How Do I get the name of a NameRange

[barnard89]

Hi

I had defined a name called "Name1 "
which refers to A1 : A500 in worksheet1

This is the code I am using

Dim str as string
str = "Name1"

Dim Range1 as Range

Range1 = Worksheets("sheet1").Range(str)

msgbox Range1.name

I expected to get Name1 as the result
Rather it is giving me a result Sheet1!A1:A500

How do I get Name1 as the output and not the range value

Please suggest

 

[firefytr]

I don't get it, you already *have* the name if it doesn't error out on you.. it's "str".

-----------
Regards,
Zack Barresse

 

[macropod]

Hi barnard89,

Form the Excel vba Help file:

Although a Range object can have more than one name, there’s no Names collection for the Range object. Use Name with a Range object to return the first name from the list of names (sorted alphabetically) assigned to the range.

Here's a way of doing that:

Sub FindRangeNames()
Dim oName As Name
Dim oRngFlag As Boolean
Dim oRng As Range
Dim oCell As Range
On Error Resume Next
For Each oName In ActiveWorkbook.Names
    Set oRng = Range(oName.Name)
    If Err = 0 Then
        If Not Intersect(ActiveCell, oRng) Is Nothing Then
            oRngFlag = True
            For Each oCell In Selection
                If Intersect(oRng, oCell) Is Nothing Then
                    oRngFlag = False
                    Exit For
                End If
            Next
            If oRngFlag = True Then _
            MsgBox "The Name " & oName.Name & " spans the selection."
        End If
        Err = 0
    End If
Next
End Sub

Cheers

[MS MVP - Word]

 

[SkipVought]

Hi,

Since I can't discern what you're really driving at, here's a great tip...

How to use the Watch Window as a Power Programming Tool faq707-4594

Step thru your code and DISCOVER what properties are exposed for your range and name objects. Check out the RefersTo property

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]