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 (43 downloads)

Join our VBA WhatsApp Group to Discuss Excel

Join Our Premium VBA WhatsApp Group

Because learning right is important