### 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__

__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__

__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.

- rngRange 2. rngRange.Offset(1)

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

__Example 3__

__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 (74 downloads)