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.

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.

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
```

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel