Table of Contents
The Intersect function in VBA is a useful tool for finding the intersection of two or more ranges in a Microsoft Excel spreadsheet. It allows the user to specify which cells or ranges they want to compare, and then returns the common cells that are shared between those ranges.
One example of using the Intersect function is to compare two lists of data and find the common elements between them. This can be useful for identifying duplicate entries or finding the overlap between two sets of data.
Another example is to use the Intersect function in conjunction with other VBA functions, such as the Count function, to perform calculations or analysis on the shared cells. This can be helpful for tasks like finding the average of a specific set of data points or counting the number of cells that meet certain criteria within the intersecting range.
In summary, the Intersect function in VBA is a powerful tool for identifying and manipulating shared data in a spreadsheet. Its versatility makes it a valuable asset for data analysis and management tasks.
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: