VBA Intersect in Excel — Find Where Two Ranges Overlap (and the Worksheet

TL;DR — Intersect returns only the cells that two ranges share. If they do not touch, it returns Nothing — not an empty range, not an error — and that Nothing is the whole point. Its number-one use is guarding a Worksheet_Change event so it only reacts to edits in the column you care about. The rule that matters most: no overlap means Nothing, so any property access without an Is Nothing check crashes with error 91:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub   ' edit wasn't in column B
    Application.EnableEvents = False                                  ' stop our own write from re-firing this
    Target.Offset(0, 1).Value = Now                                  ' stamp the time next to it
    Application.EnableEvents = True
End Sub

Union combines ranges; Intersect is its opposite — it shrinks to the shared part. And unlike every reference before it, Intersect can legitimately return nothing at all, because two ranges that do not overlap have no common cells. This guide is built on one idea — Intersect answers a yes/no question ("do these overlap?") by returning either the overlap or Nothing, and you must test which before you touch it. That single habit is what separates a robust event handler from one that crashes on the wrong click.

What you'll learn

The mental model — Intersect returns the overlap of two ranges, or Nothing if they miss
The rule that matters most — no overlap is Nothing, so guard with Is Nothing or hit error 91
The killer use — the Worksheet_Change guard, and the EnableEvents trap that comes with it
Restricting a macro to a target area with Intersect(Selection, ...)
Why Intersect (∩, overlap) is the opposite of Union (∪, combine)
Multi-range intersects and the same-sheet rule

The mental model: Intersect is the overlap of two ranges

Hand Intersect two ranges and it returns a reference to only the cells that are in both:

Dim shared As Range
Set shared = Intersect(Range("A1:D10"), Range("C5:F20"))
' shared is C5:D10 - the rectangle the two ranges have in common

If the two ranges share nothing, there is no common rectangle to return, so Intersect returns Nothing. That is the behavior that makes it useful: you rarely care about the overlap cells themselves — you care whether there is an overlap at all. "Is this cell inside that region?" becomes "is Intersect(cell, region) something rather than Nothing?" Intersect turns a geometry question into a simple presence test.

The rule that matters most: no overlap returns Nothing

Because a miss returns Nothing, reaching for any property or method on the result without checking first is a crash waiting to happen:

' FRAGILE - error 91 the moment the ranges don't overlap:
MsgBox Intersect(Selection, Range("B:B")).Address

When Selection is not in column B, Intersect is Nothing, and .Address on Nothing raises error 91, "Object variable or With block variable not set." The fix is always to test with Is Nothing first, and the idiomatic phrasing uses If Not ... Is Nothing to mean "if there is an overlap":

If Not Intersect(Selection, Range("B:B")) Is Nothing Then
    MsgBox "Your selection touches column B."
End If

Read If Not (overlap) Is Nothing as "if the overlap is not nothing" — that is, if it exists. The two mistakes here are dropping the Not (which inverts the logic) and dropping the Is Nothing check (which crashes on a miss). Memorize the full phrase; it is one of the most-typed lines in all of VBA.

The killer use: the Worksheet_Change guard

Here is where Intersect earns its place. A Worksheet_Change event fires on every edit anywhere on the sheet, and 99% of the time you only want to react to changes in one column or region. Without a guard, your handler runs on every keystroke across the whole sheet — slow and intrusive. Intersect is the guard: it asks whether the changed cell (Target) overlaps the range you are watching.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only act when the edit lands in the data range B2:B1000:
    If Intersect(Target, Me.Range("B2:B1000")) Is Nothing Then Exit Sub

    Application.EnableEvents = False          ' CRITICAL - see below
    On Error GoTo CleanExit
    Dim c As Range
    For Each c In Intersect(Target, Me.Range("B2:B1000")).Cells
        c.Offset(0, 1).Value = "edited " & Format(Now, "yyyy-mm-dd hh:nn")
    Next c

CleanExit:
    Application.EnableEvents = True           ' always restore, even after an error
End Sub

Two things make this the correct pattern. First, the guard line If Intersect(...) Is Nothing Then Exit Sub bails out early on any edit outside the target, so the body only runs when it should. Second — and this is the trap that catches everyone — the handler writes to a cell, and writing to a cell fires Worksheet_Change again, which writes again, and so on. Application.EnableEvents = False breaks that recursion, and it must be restored in a CleanExit label so a mid-handler error does not leave events disabled for the rest of the session (see VBA On Error and Worksheet_Change). The Intersect guard and the EnableEvents guard are siblings — you almost never want one without the other.

Restricting a macro to a target area

The same overlap test scopes an ordinary macro to a region. Suppose a formatting macro should only touch cells the user selected within the print area — intersect the selection with the region and work on the result:

Dim scope As Range
Set scope = Intersect(Selection, Range("PrintArea"))
If scope Is Nothing Then
    MsgBox "Select cells inside the print area first."
    Exit Sub
End If
scope.Font.Bold = True   ' only the selected cells that fall inside PrintArea

This is a cleaner pattern than checking each selected cell's address by hand: Intersect computes the in-bounds subset in one call, and the Is Nothing branch handles "the selection missed the area entirely."

Intersect vs Union: overlap versus combine

Intersect (∩) and Union (∪) are the two set operators on ranges, and they pull in opposite directions. Union(A, B) grows a reference to everything in either range. Intersect(A, B) shrinks it to only what is in both, or Nothing. Union never returns Nothing (you gave it real ranges to combine); Intersect returns Nothing all the time, and that is its feature, not a flaw. Use Union to assemble scattered cells for a batch operation, and Intersect to test or restrict — to ask whether an edit, a selection, or a cell falls inside a region you care about.

Multi-range intersects and the same-sheet rule

Intersect accepts more than two ranges and returns the cells common to all of them — Intersect(rngA, rngB, rngC) is the region every argument covers. As with Union, every range must be on the same worksheet, or you get error 1004. And the same discipline applies to the result: if the intersection could be empty, test Is Nothing before using it — with three or more ranges an empty overlap is even more likely than with two.

How ExcelMaster helps

Intersect is a small method that hides two easy-to-forget rules: check Is Nothing before you touch the result, and pair the event guard with EnableEvents = False so a self-triggered change does not spiral. Miss the first and you crash with error 91; miss the second and a Worksheet_Change handler re-fires itself into a flicker or a hang.

ExcelMaster lets you describe the behavior instead. Say "when someone edits column B, stamp the time in column C," and it writes the Intersect(Target, ...) guard, the early Exit Sub, the EnableEvents toggle, and the CleanExit restore — the whole robust event pattern, not just the happy path. You keep the workbook and the code; you skip the debugging session where the handler fired on the wrong cell or looped on itself.

Frequently asked questions

What does the Intersect method do in Excel VBA?

Intersect returns a range of the cells that two or more ranges have in common — their overlap. Intersect(Range("A1:D10"), Range("C5:F20")) returns C5:D10. If the ranges do not overlap at all, Intersect returns Nothing, which is why it is used to test whether one range falls inside another.

Why does Intersect return Nothing and crash my code?

When the ranges share no cells, Intersect returns Nothing rather than an empty range. Accessing any property on it — .Address, .Cells, .Value — then raises error 91. Always test first with If Not Intersect(a, b) Is Nothing Then (meaning "if there is an overlap") before using the result.

How do I use Intersect in a Worksheet_Change event?

Guard the handler so it only reacts to edits inside your target range: If Intersect(Target, Me.Range("B2:B1000")) Is Nothing Then Exit Sub. Then set Application.EnableEvents = False before writing any cell — otherwise your write re-triggers Worksheet_Change — and restore it to True in a CleanExit label so an error cannot leave events disabled.

What is the difference between Intersect and Union in VBA?

They are opposite set operations. Intersect returns only the cells in both ranges (the overlap), or Nothing if they do not touch. Union returns the cells in either range (the combined area). Use Intersect to test or restrict a region and Union to assemble scattered cells into one reference.

Can Intersect take more than two ranges?

Yes. Intersect(rngA, rngB, rngC) returns the cells common to all of the ranges. Every range must be on the same worksheet, and the result can be Nothing if the ranges share no common cell — which is more likely with three or more ranges, so always check Is Nothing.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-16.

Related guides: VBA Union · VBA SpecialCells · VBA Worksheet_Change · VBA On Error · VBA Range

VBA Intersect in Excel — Find Where Two Ranges Overlap (and the Worksheet_Change Guard Everyone Uses)