• hi,

    is there any way to know what is the name of a range according to an activecell.
    my problem is that returns the name of the worksheet.


  • Interesting...
    Maybe there is a more efficient way? I would have though it would be easier? Anyway, this will return the name of the activecell. This assumes that your named range refers to one single cell only. If you are looking to find the name(s) which are intersected by the activecell then the code listed will need to be edited. Checks workbook global and worksheet local scope names.


  • The variable "RangeName" will have the property of your ActiveCell if it is named.

    Sub ActiveCellRangeName()
    Dim RangeName
    Set CellNames = ActiveWorkbook.Names
    For n = 1 To CellNames.Count
    If ActiveCell.Address = Names(n).RefersToRange.Address Then
    RangeName = CellNames(n).name
    End If

    End Sub

    Regards, Joel

  • Hi rarat

    Here is a method I have used in the past. It accounts for the ActiveCell being a single cell named range, or part of a multi cell range name.

    Sub IsNamed()
    Dim nCell As Name
        For Each nCell In Names
            If Not Intersect(ActiveCell, Range(nCell.Name)) Is Nothing Then
               MsgBox nCell.Name
            End If
        Next nCell
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!