How to find if a range is included in another range using VBA?

Question

I have a problem comparing two ranges. For simplicity I will take two simple ranges M6:M10 and M6:M8, I want to know if the second one is included into the first one and the first thing I though is to write

    Sub example()
    Dim range1, range2, inte As range
        Set range1 = range("M6:M10")
        Set range2 = range("M6:M8")
        Set intersec = Intersect(range1, range2)
        If intersec = range2 Then
            [if statement]
        End If
    End Sub

But this procedure returns me the following error:

PRB: Error 13 (Type Mismatch) & Error 3061 w/ SQL Queries

So maybe I can't use the method "intersect" in this way...any hint on how to test range's inclusion? Thank you very much!

`Dim range1, range2, inte As range` declares `range1` and `range2` to be variants (not that this is the problem) Also -- the error that you mention seems unrelated to the code that you show but is likely to be hidden in the square brackets. — John Coleman, Apr 11 '16 at 11:42
also the intersect method Returns nothing if it is no intersect, could be a Problem. you should check for if intersect is Nothing first — Doktor OSwaldo, Apr 11 '16 at 11:45
Can you confirm which line generates the error? Suspect it will be `If intersec = range2 Then`. If you are trying to confirm that intersec and range2 completely overlap try: `If intersec.Address = range2.Address Then`. — David Rushton, Apr 11 '16 at 13:14

Gary's Student · Accepted Answer · 2018-11-09T11:20:55.663

7

Here is one way:

Sub ProperSubSet()
    Dim range1 As Range, range2 As Range, inte As Range
    Dim r As Range

    Set range1 = Range("M6:M10")
    Set range2 = Range("M6:M8")

    For Each r In range2
        If Intersect(r, range1) Is Nothing Then
            MsgBox "range2 is not a proper subset of range1"
        Exit Sub
        End If
    Next r
    MsgBox "range2 is a proper subset of range1"
End Sub

edited Nov 09 '18 at 11:20

answered Apr 11 '16 at 11:50

Gary's Student

95,722
10
59
99

Harley B · Answer 2 · 2016-04-11T11:57:25.010

First, declare your range1 and range2 variables as ranges.

Then when you're comparing the intersec variable to the range2 variable, use the address property of the range method to compare the contents.

Something like:

Sub example()
Dim range1 As Range, range2 As Range, intersec As Range
    Set range1 = Range("M6:M10")
    Set range2 = Range("M11:M12")
    Set intersec = Intersect(range1, range2)
    If Not intersec Is Nothing Then
        If intersec.Address = range2.Address Then
            '[CODE GOES HERE]
        End If
    End If
End Sub

score 2 · Answer 3 · answered Apr 11 '16 at 11:56

Here is something that you can experiment with:

Sub Test()
    Dim R1 As Range, R2 As Range, R3 As Range

    Set R1 = Application.InputBox("Select first range", , , , , , , 8)
    Set R2 = Application.InputBox("Select second range", , , , , , , 8)

    Set R3 = Intersect(R1, R2)
    If Not R3 Is Nothing Then
        If R3.Address = R1.Address Then
            MsgBox "First Range is subset of second"
        ElseIf R3.Address = R2.Address Then
            MsgBox "Second Range is subset of first"
        Else
            MsgBox "Neither range contained in the other"
        End If
    Else
        MsgBox "Ranges are disjoint"
    End If

End Sub

score 1 · Answer 4 · answered Apr 11 '16 at 11:49

1

The way I am using it is like this:

If Application.Intersect(rng1, rng2) Is Nothing Then 
    'herecomesthecode
Else
    'herecomesthecode
End if

You may remove the else or write Not nothing, depending on what you want to achieve.

answered Apr 11 '16 at 11:49

Vityata

42,633
8
55
100

score 0 · Answer 5 · answered Apr 11 '16 at 13:30

another additional variant:

Sub ProperSubSet2()
    Dim range1 As Range, range2 As Range
    Set range1 = [M6:M10]
    Set range2 = [M6:M8]
    Set rComp = Intersect(range2, range1)

    If Not rComp Is Nothing Then
        If rComp.Cells.Count = range2.Cells.Count Then
            MsgBox "range2 is a proper subset of range1"
        Else
            MsgBox "range2 is not a proper subset of range1"
        End If
    Else
        MsgBox "Both ranges aren't intersected at all!"
    End If

End Sub

score 0 · Answer 6 · answered Apr 12 '16 at 23:39

You can do a comparison of the intersect to the ranges to work out whether one range is contained within another. Some code to show this...

Sub TestExample()
    Dim Range1 As Range: Set Range1 = Range("M6:M10")
    Dim Range2 As Range: Set Range2 = Range("M6:M8")
    MsgBox Example(Range1, Range2)
End Sub

Function Example(Range1 As Range, Range2 As Range) As Integer
    Dim Overlay As Range: Set Overlay = Application.Intersect(Range1, Range2)
    If Not Overlay Is Nothing Then
        If Overlay.Address = Range1.Address Then Example = Example + 1
        If Overlay.Address = Range2.Address Then Example = Example + 2
    End If
End Function

The function will return 0 if no range is fully contained within another, 1 if the first range is contained within the second, 2 if the second range is contained in the first and 3 if the ranges are equal

score 0 · Answer 7 · answered Jan 15 '18 at 12:26

For a more robust solution that works on ranges with multiple areas, ranges on different worksheets from one another, ranges with very large numbers of cells (so .CountLarge, not .Count) then this will work:

Function RangeContainsRange(BigRange As Range, SmallRange As Range) As Boolean
    If BigRange.Parent Is SmallRange.Parent Then
        RangeContainsRange = Application.Union(BigRange, SmallRange).Cells.CountLarge = BigRange.Cells.CountLarge
    Else
        RangeContainsRange = False
    End If
End Function

How to find if a range is included in another range using VBA?

7 Answers7

Linked