Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel VBA Intersect Method

Using the Intersect Method in Excel VBA

Got any Excel 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 See Workbook Events and Workbook Open Event

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.

Go here to learn more about Excel Events

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.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft