How to find all internal ranges in a specified range

Question

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.

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:

Answer 1

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