Using Intersection to Create a Range in Excel VBA

Intersect Method returns a Range object that represents the Intersection of Ranges.

Example selects the Intersection of 2 Ranges (A1:D5 and C3:C10). If the Intersection is blank, the example displays a message box:

Private Sub UseIntersection()
    IntersectRanges Range("A1:D5"), Range("C3:C10")
End Sub
Private Sub IntersectRanges(range1 As Range, range2 As Range)
    Dim intRange As Range
    ' Application.Intersect Method
    Set intRange = Application.Intersect(range1, range2)
    If intRange Is Nothing Then
        ' No Intersection
        MsgBox "Ranges Do Not Intersect!"
        ' Show new Range's address
        MsgBox (intRange.Address)
        ' Select new Range
    End If
End Sub


comments powered by Disqus