CODEWITHSUNDEEP
vbaexcelrangeinclusion
Bmb58
Bmb58

Reputation: 155

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

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!

Upvotes: 5

Views: 10316

Answers (8)

Sasha Fedorov
Sasha Fedorov

Reputation: 11

Function is_subrange(rn1 As Range, rn2 As Range) As Boolean
    Dim rn As Range
    is_subrange = True
    For Each rn In rn1.Cells
        If Intersect(rn, rn2) Is Nothing Then
            is_subrange = False
            Exit For
        End If
    Next
End Function

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96763

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

Upvotes: 7

Philip Swannell
Philip Swannell

Reputation: 935

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

Upvotes: 2

Tragamor
Tragamor

Reputation: 3634

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

Upvotes: 0

Vasily
Vasily

Reputation: 5782

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

Upvotes: 0

Harley B
Harley B

Reputation: 569

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

Upvotes: 3

John Coleman
John Coleman

Reputation: 51998

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

Upvotes: 2

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 1

Related Questions