Excel VBA Intersect: Find Overlapping Ranges in a Sheet

VBA & macros · Updated November 21, 2026

In Excel VBA, use Application.Intersect to find the cells shared by two or more ranges. Assign its result to a Range variable, then check whether that variable is Nothing before using it; if the ranges do not overlap, the result is Nothing.

What Intersect Means in VBA

An intersection is the portion of cells that belongs to every range you provide. For example, the ranges B2:D8 and D5:F10 share the cells D5:D8, so their intersection is D5:D8. Ranges B2:C4 and D2:E4 do not share any cells, so their intersection is empty.

This method is useful when code needs to determine whether a particular cell or changed range falls inside an area of interest. A common application is a worksheet event: when a user edits a cell, VBA can check whether the edit occurred in a watched range and respond only when it did. Intersect does not inspect cell values, search for text, or change a cell by itself. It returns a range object describing the shared cells, leaving your code to decide what to do next.

The method is available through Excel’s Application object, so the conventional call is Application.Intersect. The ranges supplied to one call must refer to the same worksheet. If they refer to different worksheets, VBA raises an error rather than returning an empty result. This restriction is important when ranges come from different workbooks or are assembled dynamically: check their worksheet references before calling the method.

Syntax and a Safe Basic Pattern

The method accepts two required range arguments and can accept additional range arguments, up to 30 in total. Its general form is Application.Intersect(Arg1, Arg2, ...). Each argument must be a Range object. In practice, assign the result with Set, because the result is an object, and test it with Is Nothing before reading its address or changing its cells.

Sub CheckOverlap()
    Dim ws As Worksheet
    Dim firstRange As Range
    Dim secondRange As Range
    Dim overlap As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set firstRange = ws.Range("B2:D8")
    Set secondRange = ws.Range("D5:F10")
    Set overlap = Application.Intersect(firstRange, secondRange)

    If Not overlap Is Nothing Then
        MsgBox overlap.Address(False, False)
    Else
        MsgBox "The ranges do not overlap."
    End If
End Sub

With the specified ranges, the message displays D5:D8. The Address(False, False) arguments omit dollar signs and the sheet name from the displayed address; they do not alter the range. If the two ranges were B2:C4 and D2:E4, the else branch would run because there are no shared cells.

Qualifying each range with ws makes the worksheet explicit. An unqualified expression such as Range("B2:D8") refers to the active sheet in the current context, which can change as a macro runs or a user changes the selection. Explicit qualification reduces accidental references to the wrong sheet.

Examples for Cells, Rows, and Columns

Intersect works with ranges of different shapes. A cell can be tested against a table range, and a row or column can be tested against another range. For example, intersecting ws.Range("D7") with ws.Range("B2:F20") returns D7, while testing cell G7 against that same table range returns Nothing.

You can also find the portion of a column that lies inside a rectangular block. Intersecting ws.Columns("D") with ws.Range("B2:F20") returns D2:D20. Intersecting ws.Rows(7) with that block returns B7:F7. These patterns are handy when a procedure receives a full row or column but should operate only within a defined data area.

To test one cell without storing the result, you can still use a Range variable for clarity:

Sub CheckOneCell()
    Dim ws As Worksheet
    Dim targetCell As Range
    Dim watchedArea As Range
    Dim overlap As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set targetCell = ws.Range("D7")
    Set watchedArea = ws.Range("B2:F20")
    Set overlap = Application.Intersect(targetCell, watchedArea)

    If Not overlap Is Nothing Then
        MsgBox "D7 is inside the watched area."
    End If
End Sub

For a fixed address, it may be tempting to compare strings or use a row-and-column calculation instead. Intersect is often clearer when the relevant areas are themselves represented as Range objects, particularly if those areas are built from named ranges or worksheet selections.

Using Intersect in Worksheet Change Events

A frequent question is how to run code only when an edit occurs in a particular part of a sheet. The worksheet’s Worksheet_Change event receives a Target range representing the changed cell or cells. Intersect can compare that target with the area the code cares about. Put the event procedure in the code module for the relevant worksheet, not in a standard VBA module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watched As Range
    Dim changed As Range

    Set watched = Me.Range("B2:B20")
    Set changed = Application.Intersect(Target, watched)

    If Not changed Is Nothing Then
        MsgBox "A watched cell changed."
    End If
End Sub

Here, Me means the worksheet whose module contains the event procedure, so the watched area is on the same sheet as Target. If a user pastes values into several cells, Target can contain multiple cells; the intersection still identifies any part of that change that lies in B2:B20. The message appears if at least one changed cell intersects the watched area.

Be cautious if an event handler writes to cells. A write can trigger another change event and cause repeated execution. If the handler must update cells, use a planned event-control pattern and restore events even if an error occurs. The sample above only displays a message, so it does not write to the worksheet or create that particular recursion risk.

Noncontiguous Ranges and Multiple Areas

A Range object can contain multiple separate areas, such as A1:A5 together with C1:C5. You can create one with Union, then intersect that result with another range on the same worksheet. If the union of A1:A5 and C1:C5 is intersected with A3:C3, the result contains A3 and C3 as separate areas. It does not include B3, because B3 was not part of the original union.

Sub CheckMultipleAreas()
    Dim ws As Worksheet
    Dim selectedAreas As Range
    Dim testRow As Range
    Dim overlap As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set selectedAreas = Application.Union(ws.Range("A1:A5"), ws.Range("C1:C5"))
    Set testRow = ws.Range("A3:C3")
    Set overlap = Application.Intersect(selectedAreas, testRow)

    If Not overlap Is Nothing Then
        MsgBox overlap.Address(False, False)
    End If
End Sub

For this example, the resulting address represents A3 and C3, which are separate areas. Code that processes such a result should not assume it is one rectangular block. You can examine its Areas collection when you need to handle each contiguous part individually. Remember that an intersection result can itself be multi-area, and an empty result still needs the usual Is Nothing check.

When to Use Intersect Instead of Alternatives

Use Intersect when the question is spatial: do these worksheet ranges share any cells, and if so, which cells? It is a natural fit for event checks, restricting an operation to a defined section, and testing whether a selection includes a target area. Since it returns the shared Range object, your code can use that result to inspect addresses, loop through cells, or apply formatting.

Other tools answer different questions. Union combines ranges rather than finding their shared cells. A conditional statement that checks a cell’s value is appropriate when the condition concerns its contents, not its location. Worksheet functions such as COUNTIF can count values meeting criteria, but they do not replace Intersect for testing whether two Range objects overlap. Picking the method that matches the question makes code easier to understand and avoids unnecessary address parsing.

For a simple yes-or-no check, Intersect followed by If Not result Is Nothing is usually direct. If you need the actual overlapping cells, keep the result and use it. If the ranges may be on different sheets, decide what cross-sheet behavior you want first: Intersect cannot compare them in one call, so code must validate or redesign the inputs rather than expecting a harmless empty result.

Common Errors and How to Avoid Them

  • Using ranges from different worksheets: This causes a run-time error. Qualify both ranges and confirm that their Worksheet properties refer to the same worksheet before calling Intersect.
  • Using an uninitialized result: If there is no overlap, the result is Nothing. Trying to read overlap.Address before testing it causes an object-variable error. Check with If Not overlap Is Nothing Then.
  • Forgetting Set: Range variables are object variables. Use Set overlap = Application.Intersect(...), not a value assignment without Set.
  • Leaving a range unqualified: A bare Range reference can depend on the active sheet. Use ws.Range or Me.Range where appropriate.
  • Assuming a single rectangular result: Inputs can be multi-area, and the result can contain separate areas. Do not rely on a single top-left and bottom-right address to describe every result.
  • Passing an invalid object: Each argument should be a valid Range object. If a range is created from a name or user input, validate that it resolves to the intended cells before using it.

When debugging, inspect the expressions immediately before the call: confirm the workbook, worksheet, and address for each range. In the VBA editor, the Immediate window can help examine object properties while execution is paused. Testing one known-overlap case and one known-nonoverlap case is a simple way to verify that the branches behave as intended.

Running VBA Safely in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic on the ribbon. On Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel app to use this code.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, and an event procedure can run automatically when a user edits a cell. Save the workbook as a macro-enabled .xlsm file if it needs to retain VBA code. The examples here only inspect ranges or display a message, but the same safety practice matters when adapting them to change worksheet data.

Frequently Asked Questions

What does Intersect return when ranges do not overlap?
It returns Nothing. Store the result in a Range variable and test it with If result Is Nothing Then before accessing properties such as Address.

Can Intersect check ranges on different sheets?
No. The range arguments in a call must refer to the same worksheet. A cross-sheet call raises an error; qualify the ranges and validate their worksheet references first.

Can Intersect handle more than two ranges?
Yes. It accepts two required arguments and additional Range arguments, up to 30 total. The result contains cells common to every supplied range.

Why does my Worksheet_Change code run for a paste?
Target can represent multiple changed cells when a user pastes a block. Intersect returns the part of that target that lies in the watched range, so the event runs if any changed cell overlaps it.

Does Intersect change cells or compare their values?
No. It returns the shared range object only. Use separate VBA statements to read or change those cells, or a value-based method when your condition concerns cell contents.

Can I run VBA Intersect in Excel for the web?
No. Excel for the web does not execute VBA macros. Use desktop Excel for Windows or Mac to run the VBA code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.