excelvbaforeachrange

How to find all internal ranges in a specified range


How do I find all the internal name ranges that are within a specified range?

For example, there are three ranges: R_1, R_2 and R_3.
enter image description here

In this example, the specified range is R_2.
I need to find R_2 and R_3.

Dim nm As Name

For Each nm In ThisWorkbook.Names
  *** CODE HERE ***
Next nm

I previously used the following code.

Dim nm As Name
Dim rng As Range

Set rng = Range("R_2")  'specified range

For Each nm In ThisWorkbook.Names
    If Not Intersect(rng,Range(nm)) Is Nothing Then
        *** CODE HERE ***
    End If
Next nm

It worked if I needed to find all internal ranges, but no external ranges existed.
That is, if there was no range R_1, I would have no problem finding R_2 and R_3 but if there is an external range, then searching for intersections is not appropriate.

The names of ranges can be different, so I need exactly the enumeration of all ranges (for each).


On the basis of the suggested answers, I formed a solution:

Sub Test()
Dim nm As Name
Dim rng As Range

Set rng = Range("R_2") 'example specified range

For Each nm In ThisWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then 'ranges only from the active sheet
        If isInside(rng, nm) And Not rng.Name = nm Then Debug.Print nm.Name
    End If
Next nm

End Sub


Function isInside(rb As Range, ra As Excel.Name) As Boolean
Dim r As Range
   
Set r = Intersect(ra.RefersToRange, rb)

If Not r Is Nothing Then
    isInside = (ra.RefersToRange.Cells.CountLarge = r.Cells.CountLarge)
    Exit Function
End If

End Function

In my project, this code accomplishes the task, even if there are ranges that are contiguous with boundaries.

Second example image:
enter image description here


Solution

  • To find the names of the named areas you need a secure loop. From the heart of the loop, the function that does the checking is called. Checks if the number of cells of the intersection of the two regions is equal to the cells of the checked region. If so, then the controlled region is entirely within our original region.

    Public Function getInnerRanges(specificRange As String) As String
       Dim nRng As Name, r As Range, rng As Range
       Set rng = Range(specificRange)
       getInnerRanges = specificRange
       For Each nRng In ThisWorkbook.Names
          If specificRange <> nRng.Name Then
             If isInside(rng, nRng) Then
                getInnerRanges = getInnerRanges & "," & nRng.Name
             End If
          End If
       Next
    End Function
    
    Public Function isInside(rb As Range, ra As Excel.Name) As Boolean
       Dim r As Range
       On Error GoTo Lerr
       Set r = Intersect(ra.RefersToRange, rb)
       If Not r Is Nothing Then
          isInside = (ra.RefersToRange.Cells.CountLarge = r.Cells.CountLarge)
          Exit Function
       End If
       On Error GoTo 0
       Exit Function
    Lerr:
       On Error GoTo 0
       isInside = False
    End Function
    
    Sub example22()
       Dim s As String
       s = getInnerRanges("R_2")
       Debug.Print s
    End Sub