Got any Excel Questions? Free Excel Help
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
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
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 firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates