How to Use Intersect in VBA (With Examples)

---

You can use the Intersect method in VBA to return a range that represents the intersection of two specific ranges.

Here is one common way to use this method in practice:

Function FindIntersect(range1, range2)
    FindIntersect = Application.Intersect(range1, range2)
End Function

You can then type this function directly into an Excel spreadsheet, specifying two ranges, and the function will return each of the values from the range that represents the intersection of those two ranges.

The following examples show how to use this function in practice with the following dataset in Excel:

Example 1: Find Intersection of Two Ranges (With One Cell as Result)

Suppose we would like to return the value of the cell that represents the intersection between the range A2:C2 and A1:A11.

We can define the following function in VBA:

Function FindIntersect(range1, range2)
    FindIntersect = Application.Intersect(range1, range2)
End Function

We can then type this function directly into a cell in our spreadsheet:

The formula returns the value Mavs, which represents the intersection of the range A2:C2 and A1:A11:

Example 2: Find Intersection of Two Ranges (With Multiple Cells as Result)

Suppose we would like to return the entire range of cells that represents the intersection between the range A1:C3 and A1:B10.

We can define the following function in VBA:

Function FindIntersect(range1, range2)
    FindIntersect = Application.Intersect(range1, range2)
End Function

We can then type this function directly into a cell in our spreadsheet:

The formula returns the entire rectangular range of values that represents the intersection of the range A1:C3 and A1:B10:

Note: You can find the complete documentation for the VBA Intersect method .