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 here.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Used Columns

VBA: How to Count Rows in Table

VBA: How to Count Number of Sheets in Workbook