VBA Intersect

What is Excel VBA Intersect?

VBA Intersect is a function that gives the middle point of two or more given range addresses. Hence, we have to give at least a minimum of two ranges to get their intersection point. Usually, the word Intersect is used in mathematics geometry when two or more lines cross each other. VBA Intersect is no different. It will fetch the intersecting point of the ranges. For example, look at the following data in an Excel worksheet.

Assume we have two ranges, i.e., range B1 to B16 and range A10 to D10. We can use the following VBA Intersect code to get the intersection point.

Sub Intersect_Intro()
Intersect(Range(“B1:B16”), Range(“A10:D10”)).Select
End Sub

Once we run the code, it will give the intersecting point of the two given ranges.

The intersect point of the two given ranges is B10.

What is Excel VBA Intersect?

Intersect is a function that retrieves the common intersecting cell between two ranges.
We can assign range references to two different variables and use the variable name instead of the range reference directly.
We can change the background color of the intersecting cell.
We can offset the number of rows and columns from the intersecting cell.
We can clear the content and apply formatting to intersecting cells.

How to use Excel VBA Intersect Function?

We will show you a step-by-step approach to using the VBA Intersect function. Before we move into the example, let’s understand the syntax of the VBA Intersect function.

Syntax

Arg1 As Range: In this argument, we give the first intersecting range of cells. It is a mandatory argument.
Arg2 As Range: Here, we give the second intersecting range of cells. It is also a mandatory argument.

The rest of the arguments are optional. We can give as many as 30 ranges to the VBA Intersect function.

Now, let us show you the simple steps required to apply the VBA Intersect function. Let us consider the data used in the introduction again.

Assume we need to get the intersecting cell address of two ranges, C1:C16 and A1 to A8. Let us follow the steps listed below.

Step 1: Start the sub-procedure by naming the macro.

Step 2: Define a variable with the String VBA data type because we will be storing the intersecting cell address to this variable.

Step 3: Use the defined variable and assign it the VBA Intersect function.

Step 4: Provide the first intersecting range as C1 to C16 using the VBA RANGE object.

Step 5: For the second intersecting range, provide the range as A8 to D8.

Step 6: Now, close the bracket of the VBA Intersect function and enter a dot to access all the Properties and methods associated with the function.

Step 7: As we can see, we have a list of all the Properties and methods associated with the VBA Intersect function. Since we need to get the cell address of the intersecting point, let’s use the address property.

Step 8: Now, let’s show the intersecting point cell address assigned to the variable in a message box. The following is the complete code for you to copy and use in your workbook.

Sub VBA_Intersect_Ex()
Dim Intersect_Cell As String
Intersect_Cell = Intersect(Range(“C1:C16”), Range(“A8:D8”)).Address
MsgBox Intersect_Cell
End Sub

Let’s execute the code. It shows the intersecting cell address of the two given ranges.

The intersecting cell address for two ranges, C1 to C16 and A8 to D8, is the cell C8.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples of Excel VBA Intersect

Let us show you a variety of VBA Intersect function examples. Let’s get started with the first example.

Example #1 – Assign Ranges to Variables

In the introduction example, we have entered two different ranges directly into the VBA Intersect function. The flexible way is to assign ranges to variables and use those variables inside the VBA Intersect function.

We will use the following data for this example.

Look at the following code.

Code Explanation:

Part #1: We have defined a variable to assign the first range of cells to be supplied to the VBA Intersect function. Next, we have assigned the range of cells from C1 to C16 to the defined variable.

Part #2: We have defined another variable to assign the second range of cells to the VBA Intersect function. Next, we have assigned the range of cells from A8 to D8 to the second range variable.

Part #3: Inside Intersect, we have used the variables that hold the range references. Finally, we have selected the “Select” method to select the intersecting cell. Once we run the code, it will select the intersecting cell. Here, we have not used VBA Intersect multiple ranges.

Example #2 – Change the background color of the Intersecting Cell

We will use the same data range for all the examples. This time, we will change the background color of the intersecting cell.

Let’s look at the following code.

Sub VBA_Intersect_Ex2()
‘Define a variable to assign the first range
Dim Rng1 As Range
‘Set the first range to variable
Set Rng1 = Range(“C1:C16”)
‘Define another variable to assign the second range
Dim Rng2 As Range
‘Set the first range to variable
Set Rng2 = Range(“A8:D8”)
‘Use the VBA Intersect function to change background color of the intersecting cell
Intersect(Rng1, Rng2).Interior.Color = vbBlue
End Sub

The line “Intersect(Rng1, Rng2).Interior.Color = vbBlue” assigns the blue to the intersecting cell.

Execute the code. It should change the background color of the intersecting cell to vbBlue.

As seen, the intersecting cell background color changed to blue.

Example #3 – Offset Cells from the Intersecting Cell

We can also offset the number of rows and columns in excel from the intersecting cell using the VBA Intersect row and column. For example, assume the intersecting cell is C8, and if we need to select the cell D12 from that intersecting cell, then we can use the following code.

Sub VBA_Intersect_Ex3()
‘Define a variable to assign the first range
Dim Rng1 As Range
‘Set the first range to variable
Set Rng1 = Range(“C1:C16”)
‘Define another variable to assign the second range
Dim Rng2 As Range
‘Set the first range to variable
Set Rng2 = Range(“A8:D8”)
‘Use the VBA Intersect function to offset 4 rows and 1 column from the intersecting cell
Intersect(Rng1, Rng2).Offset(4, 1).Select
End Sub

When we execute this code, it will select the cell which is four rows down and one column right of the intersecting cell.

As we can see, it has selected the cell D8, i.e., four rows down from the intersecting cell C8 and 1 column right from the intersecting cell.

Example #4 – Clear the Content from Intersecting Cell

Another interesting example is we can clear the content from the intersecting cell. The following code will clear content from the intersecting cell.

Intersect(Rng1, Rng2).ClearContents

We have used the “ClearContents” property of the VBA Intersect target to delete the data from the intersecting cell.

Sub VBA_Intersect_Ex4()
‘Define a variable to assign the first range
Dim Rng1 As Range
‘Set the first range to variable
Set Rng1 = Range(“C1:C16”)
‘Define another variable to assign the second range
Dim Rng2 As Range
‘Set the first range to variable
Set Rng2 = Range(“A8:D8”)
‘Use the VBA Intersect function to offset to clear the content
Intersect(Rng1, Rng2).ClearContents
End Sub

Once we execute this code, it will clear the content or data from the intersecting cell.

Important Things to Note

VBA Intersect requires a minimum of two ranges. If the two given ranges are not valid, it will throw out of range error.
If the subsequent property or method is not properly mapped, we would like to get an error.
While changing the background color of the intersecting cell, we can either use predefined VBA colors or we can use VBA color index codes.

Frequently Asked Questions (FAQs)

1. What does IF NOT INTERSECT mean in Excel VBA?

IF NOT Intersect means if the logical condition applied is not within the intersecting range, then perform a particular task. The following is the sample code.

If Not Intersect(Rng1, Rng2) Is Nothing Then
Execute part 1
Else
Execute part 2
End If

2. What are some practical applications of the Intersect function in Excel VBA?

One of the practical applications of the Intersect function is to clear the content from the intersecting cell as shown in Example #4 above.

3. Can I use Intersect with named ranges in Excel VBA?

Yes, we can use the named ranges inside the VBA Intersect function. For example, look at the following code.

Sub VBA_Intersect_Named_Range()
Intersect(Range(“HIII”), Range(“A8:D8”)).Select
End Sub

In this code “HIII” is the named range.

4. Is there an alternative to Intersect for finding the intersection of two ranges in Excel VBA?

No alternatives are available for the Intersect function. However, VBA UNION can be used to select all the cells within the range of intersection.

Download Template

This article must be helpful to understand the VBA Intersect, with its features and examples. You can download the template here to use it instantly.

VBA-Intersect Download

VBA Intersect