EXCELY


Free Excel Templates and Articles
  • Excel VBA
  • Using Intersection to Create a Range in Excel VBA

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!"
    Else
        ' Show new Range's address
        MsgBox (intRange.Address)
 
        ' Select new Range
        intRange.Select
    End If
End Sub