Is there a way to obtain the name of a range?

[JenniferMurphy]

Suppose I want to show the names of various named ranges in a table. In this table, for example, I have assigned the name "Price" to Column C and "Quan" to Column D. I would like to put some expression, like "=RangeName(C:C)", into C6 and D6 that would show those names and would change if I renamed those ranges.

R/C	C	D	E	F
5	Unit Price	Quantity	Cost	Formula
6	(Price)	(Quan)	()	()
7	$9.98	250	$2,495.00	E7: =Price*Quan
8	$1.29	500	$645.00	E8: =Price*Quan
9	$5.49	130	$713.70	E9: =Price*Quan

<tbody>
</tbody>

If this does not exist, can I write a UDF that will do the job?

Thanks

 

[shg]

From the Immediate window

? Range("C:C").Name.Name

Depends on how the ranges are names, however.

 

[shg]

E.g.,

Sub JM()
  With Range("C:C")
    .Worksheet.Names.Add Name:="JM", RefersTo:=.Cells
    Debug.Print .Name.Name
    .Worksheet.Names("JM").Delete
    
    .Worksheet.Names.Add Name:="Jennifer", RefersTo:=.Cells
    Debug.Print .Name.Name
    .Worksheet.Names("Jennifer").Delete
    
    .Worksheet.Parent.Names.Add Name:="JM", RefersTo:=.Cells
    Debug.Print .Name.Name
  End With
End Sub

 

[JenniferMurphy]

From the Immediate window

? Range("C:C").Name.Name

Depends on how the ranges are names, however.

Cool!

Here's what I came up with:

Function RangeName(pRange As String) As String

Dim pieces() As String
RangeName = Range(pRange).Name.Name
pieces = Split(RangeName, "!")
RangeName = pieces(1)
   
End Function

It works!

Is there a way to make it simpler? Can I get the result from the Range function without the sheet name?

Thanks!

 

[DanteAmor]

If you want to know the name range a cell belongs to, try this UDF
eg.
=NameRange(C2)

Function NameRange(cell As Range)
    Dim wn As Variant
    Dim rng As Range
    On Error Resume Next
    For Each wn In ThisWorkbook.Names
        Set rng = Range(wn)
        If rng.Parent.Name = cell.Parent.Name Then
            If Not Intersect(cell, rng) Is Nothing Then
                NameRange = wn.Name
                Exit Function
            End If
        End If
    Next
End Function

 

[JenniferMurphy]

Here's what I came up with:

Function RangeName(pRange As String) As String

Dim pieces() As String
RangeName = Range(pRange).Name.Name
pieces = Split(RangeName, "!")
RangeName = pieces(1)
   
End Function

And here it is in action:

R/C	C	D	E	F
5	Unit Price	Quantity	Cost	Formula
6	(Price)	(Quan)	#VALUE!	C6: ="(" & rangename(Col() & ":" & Col()) & ")"
7	$9.98	250	$2,495.00	E7: =Price*Quan
8	$1.29	500	$645.00	E8: =Price*Quan
9	$5.49	130	$713.70	E9: =Price*Quan

<tbody>
</tbody>

Two more questions:

1. If the range does not have a name assigned, I get a value error. How can I check for this so I can return a null string?
2. Is there a simpler way to call the function than the expression in C6 "=rangename(Col() & ":" & Col())" as shown in F6?

 

[JenniferMurphy]

If you want to know the name range a cell belongs to, try this UDF

Thanks, but I want to know the name of the range passed as an argument.

 

[JenniferMurphy]

E.g.,

Sub JM()
  With Range("C:C")
    .Worksheet.Names.Add Name:="JM", RefersTo:=.Cells
    Debug.Print .Name.Name
    .Worksheet.Names("JM").Delete
    
    .Worksheet.Names.Add Name:="Jennifer", RefersTo:=.Cells
    Debug.Print .Name.Name
    .Worksheet.Names("Jennifer").Delete
    
    .Worksheet.Parent.Names.Add Name:="JM", RefersTo:=.Cells
    Debug.Print .Name.Name
  End With
End Sub

Hmmm... I'm not sure what this does or how to use it. Can you help?

 

[DanteAmor]

Without argument:

Function RangeName2()


    Dim pieces() As String
    On Error Resume Next
    RangeName2 = Columns(ActiveCell.Column).Name.Name
    pieces = Split(RangeName2, "!")
    If UBound(pieces) = 1 Then RangeName2 = pieces(1) Else RangeName2 = pieces(0)
   
End Function

 

[shg]

If Price & Quant are defined as RC3 and RC4, I don't think you're going to get there.

In my example, you're asking the workbook or worksheet the name of a statically-defined range. When a UDF receives a range argument, it's already been resolved by whatever process Excel uses to evaluate arguments.