VBA Intersect Function

VBA Intersect Function

VBA intersect function is a very useful to get the intersection area of a Range. It is generally used to exclude header from range, or to check if a certain cell laying in a range or not.

Intersect function returns the Range object.  We will see couple example here to under how it works.

Example 1

Intersect function need two or more range to find the intersection range. Have a look at the image below.

In this image, we have three range, 1. Yellow, 2. Blue, 3. Green.

If we use the Intersect function by supply all three range, it will return the range in orange color.

(Note* – colors are only to explain the example.)

See how intersect will return the orange range here

Sub GetIntersect()

    Dim rngYellowRange As Range
    Dim rngGreenRange As Range
    Dim rngBlueRange As Range
    Dim rngOrangeRange As Range

    Set rngYellowRange = Sheet1.Range(“rngYellow”)
    Set rngBlueRange = Sheet1.Range(“rngBlue”)
    Set rngGreenRange = Sheet1.Range(“rngGreen”)

    Set rngOrangeRange = Intersect(rngYellowRange, rngBlueRange, rngGreenRange)
    rngOrangeRange.Select

End Sub

In the example above, we are only using three range as parameter in intersect, but we can use up to 30 different range.

Example 2

Look at the screenshot below.

Most of the time, we need data in our program, but we don’t need headers. We can use intersect to exclude our headers from data range. Have a look at the program below.

Sub SelectDataRange()

    Dim rngData As Range
    Set rngData = Sheet2.Range(“A3”).CurrentRegion
    Intersect(rngData, rngData.Offset(1)).Select   

End Sub

Above program is using two range.

  1. rngRange 2. rngRange.Offset(1)

and selecting the intersect of both ranges. Try it yourself.

Example 3

Look at the screenshot below, we want to know if user selected a cell within this yellow range.

We will need to use worksheet selection change event to make this happen. Here is the program

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ‘– Exit program if user select more than one cell
    If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Me.Range(“rngSelection”), Target) Is Nothing Then
            Me.Range(“rngMessage”).Value = “Good Selection”
        Else
            Me.Range(“rngMessage”).Value = “Bad Selection”
        End If

End Sub

You can use the file attached below to see how all these programs are working

Intersect.zip (70 downloads)

Join our VBA WhatsApp Group to Discuss Excel