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!

Answer 1

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

Answer 2

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