Excel VBA Intersect Method
Using the Intersect Method in Excel VBA
Got any Excel/VBA Questions? Free Excel Help
Excel Intersect Method
The Intersect Method will return a Range Object that represents the intersection of two, or more, ranges. It can be used to determine if a specified Range Object intersects another specified range(s). Or, in layman's terms, does a specified range intersect another range(s).
To explain this, simply copy and paste the macro below into any standard Module. Then, ensure the active cell is within A1:A10, and run the code. It will return the address of the active cell.
Sub TryMe()
MsgBox Intersect(ActiveCell, Range("A1:A10")).Address
End Sub
If the active cell is not within A1:A10 a Run Time Error occurs. This is because the Intersect Method is unable to return a Range Object, and hence any address of the Intersect. It is important to be fully aware of this with the Intersect Method as it can catch out the unaware.
Ok, so how do we overcome the possibility of a Run Time error when/if our specified range to check does not Intersect another specified range, or
ranges? To know the answer to this we must first understand what IS returned to Excel when/if a Range Object fails. What is returned is the keyword Nothing. The keyword Nothing tells Excel that what should be a Range Object is not. The Nothing keyword is mostly used to clear an Object variable back to its default of Nothing. For example, the code below would clear the Object Variable MyObject.
Set MyObject = Nothing
This would only be needed when/if the Object Variable MyObject has been Set to any Object, e.g. a Range Object.
Ok, so we now we know the meaning of Nothing, we can use code like shown below to prevent the Run Time error we get when/if the active cell does not Intersect A1:A10
Sub TryMeAgain()
If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
MsgBox "The active cell does NOT Intersect A1:A10"
Else
MsgBox "The active cell does Intersect A1:A10"
End If
End Sub
In this case, instead of a Run Time error when the active cell does not Intersect A1:A10, we get a message box telling us so.
Some Practical Uses for the Intersect Method
One of the most popular uses for the Intersect Method is to use it to determine whether a changed cell is part of another range, or ranges. If you
are not already aware, Excel has a built in Worksheet_Change Event that is fired when any change of data occurs on the Worksheet that houses the Worksheet_Change code. Excel's Event code fits under two main headings
1) Workbook Events
2) Sheet Events
Workbook Events
The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". This will
take you directly to the Private Module of the Workbook Object ("ThisWorkbook"). Then you will need to select "Workbook" from the "Object"
drop down box in the top left (where it reads "General"). This will default to the Workbook_Open Event. You can choose other Events from the "Procedure" drop down box to the right of the "Object" drop down box.
Sheet Events See Worksheet Change Event
The quickest way to get to Excels Sheet Events is to right click on the sheet name tab and select "View Code". This will take you directly to the
Private Module of the Sheet Object, e.g. "Sheet1". Then you will need to select "Worksheet" from the "Object" drop down box at the top left (where it reads "General"). This will default to the Worksheet_SelectionChange Event. You can choose other Events from the "Procedure" drop down box to the right of the "Object" drop down box.
Back to the Intersect Method. Place the code below into the Private Module of the active Worksheet, then close the VBE so that you are back to the Worksheet and change any cell by entering any data.
Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub
You will soon see that the keyword Target always returns the Range Object of the cell that fired the Worksheet_Change Event. So, armed with this knowledge we can now use the Intersect Method to take some action when/if specified cell is changed. For example;
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
MsgBox "Hello"
End If
End Sub
will only display the "Hello" message if the cell that fired the Worksheet_Change Event Intersects A1:A10 of the Sheet housing the code. Note the use of the Not Operator to reverse the logic of the If Statement, i.e TRUE when FALSE and FALSE when TRUE.
If you need to check against a non contiguous range(s) (multiple ranges not sharing common boundaries) we can use code like show below;
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1,B2,C5")) Is Nothing Then
MsgBox "Hello"
End If
End Sub
If you need to check against a non contiguous range(s) and contiguous ranges we can use code like show below;
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10,B2,C5:D10")) Is Nothing Then
MsgBox "Hello"
End If
End Sub
When/if we are checking against a named range (good practice, especially with VBA) we should first check to ensure the named ranges exists. This is needed to prevent a Run Time Error if the named range does not exist and always return Nothing from the Intersect Method. Below is how we could use the Intersect Method to check if a range (Target in this case) intersects a named range.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Range("MyRange") Is Nothing Then Exit Sub
On Error GoTo 0
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
MsgBox "Hello"
End If
End Sub
Note the use of On Error Resume Next. This is needed to stop any/all Run Time errors if the named ranges "MyRange" does not exist. While the Exit Sub tells Excel to do no more and exit the Sub Procedure. On Error GoTo 0 resets all error trapping and allows Excel to again bug out on Run Time errors. However, this does happen automatically when the Procedure ends, or encounters Exit Sub.
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
