Excel VBA Intersect Method

 

Excel VBA Intersect Method for Range

excel_intersection

In Excel VBA, Intersect Method is used to return the intersection Range (the common area in the above picture).

Syntax of Intersect Method

expression .Intersect(Range1, Range2, …)

The intersection Range must be at least 2, in order to find the intersection area.

If intersection area exists, Intersection Method returns a Range

If intersection area does not exist, Intersection Method causes a Runtime Error, which can be avoided using “Is Nothing” keyword, meaning “nothing” returns.

To add a Macro action if intersection exists:

If Not Intersect(Range1, Range2, ...) is nothing Then
Your code...
End If

To add a Macro action if intersection does not exist:

If Intersect(Range1, Range2, ...) is nothing Then
Your code...
End If

Example of Intersect Method

The below example checks if Target Range lies in column A, if it is true and value is >100, a message box pops up.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Value > 100 Then
        MsgBox ("Column A value >100")
    End If
End Sub

You can refer to the below link if you don’t understand Worksheet_Change event.

Excel VBA Worksheet.Change Event

Intersection of single Range

Since Intersect Method is used for at least 2 Cells, we cannot apply Intersect Method if you try to verify if the single target Cell falls into our action range.

To check single cell , we can simply use Address Property. The below example check if target Range is at G6.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(rowabsolute:=False, columnabsolute:=False) = "G6" Then
        MsgBox ("This cell is G6")
    End If
End Sub

Address Property returns absolute cell address with dollar sign, such as $G$6, parameters

(rowabsolute:=False, columnabsolute:=False)

remove the dollar sign and return “G6”. (of course you can also use $G$6 in the condition instead)

Outbound References

http://msdn.microsoft.com/en-us/library/office/aa195772%28v=office.11%29.aspx

http://www.ozgrid.com/VBA/vba-intersect.htm

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *