I can't figure out how to 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.

I need to search through all the ranges (for each) to find only those that will fit inside the specified range.

In this example, the specified range is R_2. At the output, I need to find R_2 and R_3. But not R_1.

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

If I needed to locate all internal ranges but there were no external ranges, it worked. That is, I would have no trouble locating R 2 and R 3 if there was no range of R 1. So looking for intersections is inappropriate if there is an external range.

Please let me know what you can come up with in this situation. I should also mention that range names can vary, so I specifically require an enumeration of all ranges (for each).

Mar 27, 2023 in Others 487 views

## 1 answer to this question.

It is simple to compare once you have the corner's coordinates. The first approach compares every range against every other range. The values are computed using the final approach.
You must choose whether to compare with and > or = and >=.

```Sub Test()
Dim nm1 As Name, nm2 As Name

For Each nm1 In ThisWorkbook.Names
For Each nm2 In ThisWorkbook.Names
If nm1.Name <> nm2.Name Then
Debug.Print nm1.Name & " inside of " & nm2.Name & ": " & IsInside(nm1.RefersToRange, nm2.RefersToRange)
End If
Next nm2
Next nm1
End Sub

Function IsInside(InnerRange As Range, OuterRange As Range) As Boolean
Dim InnerLeft As Long
Dim InnerTop As Long
Dim InnerRight As Long
Dim InnerBottom As Long
Dim OuterLeft As Long
Dim OuterTop As Long
Dim OuterRight As Long
Dim OuterBottom As Long

With InnerRange
InnerTop = .Row
InnerLeft = .Column
InnerBottom = .Row + .Rows.Count
InnerRight = .Column + .Columns.Count
End With
With OuterRange
OuterTop = .Row
OuterLeft = .Column
OuterBottom = .Row + .Rows.Count
OuterRight = .Column + .Columns.Count
End With
IsInside = False
If InnerLeft > OuterLeft And InnerRight < OuterRight Then
If InnerTop > OuterTop And InnerBottom < OuterBottom Then
IsInside = True
End If
End If
End Function```
answered Mar 27, 2023 by
• 63,720 points

## In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## how to select a statistical range in an excel

The lowest 5% and top 5% will ...READ MORE

## How to multiply by a percentage range in Excel

The following would yield an array from ...READ MORE

## Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

## Copying and pasting from one workbook to another doesn't work

Your ranges aren't fully qualified. Excel will make ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE