CODEWITHSUNDEEP
c#.netexcelvstoms-office
M. X
M. X

Reputation: 1347

Excel: Getting the surrounding named range in Excel

I have an Excel sheet with many named ranges, which have an hierarchical structure (one range contains other ranges). The ranges have no intersections and no multipel areas. How can I estimate the the surounding named range of a specific named range range (in other words I want to get something like a father-child relationship). For excample: enter image description here

Here I have range C. Now I want to estimate its "father". In this case it is B.

Upvotes: 2

Views: 125

Answers (1)

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

If the "parent" always completely contains the child and is the smallest range (in terms of cell count) that does so, then this might help. I do realise it's VBA and your tags imply VSTO but the approach may still be applicable...

Note that there are lots of cases not checked for: named formulae is one such.

Public Function ParentName(rng As Range) As String

Dim intersection As Range
Dim possibleParent As Range
Dim nm As Name

Dim rngCellCount As Long
Dim intersectCellCount As Long
Dim possParentCellCount As Long
Dim rangeParentCellCount As Long

    rngCellCount = rng.Cells.Count

    For Each nm In Names
        Set possibleParent = nm.RefersToRange
        Set intersection = Application.Intersect(rng, possibleParent) ' which cells are common between the target and possible parent?
        If Not intersection Is Nothing Then ' Nothing means no cells in common
            intersectCellCount = intersection.Cells.Count
            possParentCellCount = possibleParent.Cells.Count
            ' if intersection is same size as child then child is completely contained
            If intersectCellCount = rngCellCount And possParentCellCount > intersectCellCount Then
                If rangeParentCellCount > possParentCellCount Or rangeParentCellCount = 0 Then
                    ' record name of parent if smaller than best so far (or if we haven't already found a parent at all)
                    parentName = nm.Name
                    rangeParentCellCount = possParentCellCount
                End If
            End If
        End If
    Next

End Function

Upvotes: 2

Related Questions