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.