How to find all internal ranges in a specified range

0 votes

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.

enter image description here

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 by Kithuzzz
• 38,010 points
378 views

1 answer to this question.

0 votes

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 narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

In Excel, how to find a average from selected cells

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

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
493 views
0 votes
1 answer

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

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

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
805 views
0 votes
1 answer

how to select a statistical range in an excel

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

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
423 views
0 votes
1 answer

How to multiply by a percentage range in Excel

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

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
335 views
0 votes
1 answer

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

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
788 views
0 votes
1 answer

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

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

answered Feb 18, 2023 in Others by narikkadan
• 63,420 points
270 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
916 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

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

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,240 views
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
544 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
3,139 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP