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.

[TABLE="class: grid, width: 420"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"](Price)[/TD]
[TD="align: center"](Quan)[/TD]
[TD="align: center"]()[/TD]
[TD="align: center"]()[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]$9.98[/TD]
[TD="align: center"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]E7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]$1.29[/TD]
[TD="align: center"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]E8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]$5.49[/TD]
[TD="align: center"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]E9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 650"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"](Price)[/TD]
[TD="align: center"](Quan)[/TD]
[TD="align: center"]#VALUE![/TD]
[TD]C6: ="(" & rangename(Col() & ":" & Col()) & ")"[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]$9.98[/TD]
[TD="align: center"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]E7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]$1.29[/TD]
[TD="align: center"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]E8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]$5.49[/TD]
[TD="align: center"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]E9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

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.