• Determine if a cell is within a named range

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Determine if a cell is within a named range

    Author
    Topic
    #352514

    Hi,

    This is something I’ve just written because I certainly have a use for it.

    I often have a need to now if a particular cell is within a named range.

    Because I’m not so great on the user side of Excel, where there’s probably an inbuilt function to do it, I’ve written a small routine, which for a cell which I’ve sleected, tells me any named ranges that cell occurs in.

    Sub ListNamedRanges()
    Dim xlnName As Excel.Name
    Dim rngNamedRange As Range
    Dim rngcell As Range
    Dim strOutput As String
    Set rngcell = Selection(1, 1)
    For Each xlnName In ActiveWorkbook.Names
      On Error Resume Next
      Set rngNamedRange = xlnName.RefersToRange
      If rngNamedRange.Parent.Name = ActiveSheet.Name Then
        If Union(rngNamedRange, rngcell).Address = rngNamedRange.Address Then
          strOutput = strOutput & vbCrLf & xlnName.Name
        End If
      End If
    Next
    MsgBox strOutput
    End Sub
    

    Acknowledgements to John Walkenbach- “Excel 2000 Power Programming with VBA” for using “union” to determine if one range is within another.

    Viewing 1 reply thread
    Author
    Replies
    • #513314

      Geoff,

      I don’t think there is a built in function. If anyone knows better, I would love to be corrected.

      Brooke

    • #513330

      Am fairly certain there’s no built-in function.
      If you wanted to know if a cell was part of one particular named range, then you could use a custom function as follows:

      Private Function WithinRange(rngCell As Excel.Range, rngName As Excel.Range) As Boolean
      If Intersect(rngCell.Resize(1, 1), rngName) Is Nothing Then
      WithinRange = False
      Else
      WithinRange = True
      End If
      End Function

      Then in an empty cell you’d enter something like =WithinRange(A1, NamedRange)
      which would display either TRUE or FALSE depending on whether or not cell A1 is part of NamedRange.

    Viewing 1 reply thread
    Reply To: Determine if a cell is within a named range

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: