WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Intersect

VBA Intersect

Excel VBA Intersect

VBA Intersect is used to get a range object that is an intersection of two or more range. The minimum of two ranges should be supplied to find the intersecting range point. All the other arguments are optional based on the requirement.

Below is the syntax of the VBA INTERSECT formula.

Intersect Formula

  • Arg1 as Range: First intersecting range.
  • Arg2 as Range: Second intersecting range.

In the below examples we will see some of the useful techniques.

VBA Intersect

Examples

You can download this VBA Intersect Excel Template here – VBA Intersect Excel Template

Example #1

For example, use the below data.

VBA Intersect Example 1

Step 1: Declare the variable as Variant.

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

End Sub

VBA Intersect Step 1

Step 2: For this variable assign the value through the Intersect formula.

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

  MyValue = Intersect(

End Sub

VBA Intersect Step 2

Step 3: Select the first range as B2 to B9.

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

  MyValue = Intersect(Range("B2:B9"),

End Sub

VBA Intersect Step 3

Step 4: Select the second range from A5 to D5.

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

  MyValue = Intersect(Range("B2:B9"),Range("A5:D5")

End Sub

VBA Intersect Step 4

Step 5: We are testing with only two ranges here. Close the formula and select the method as a VBA Cell Address.

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

  MyValue = Intersect(Range("B2:B9"), Range("A5:D5")).Address

End Sub

Step 5

Step 6: Show the value in the message box in VBA.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Intersect_Example()

  Dim MyValue As Variant

  MyValue = Intersect(Range("B2:B9"), Range("A5:D5")).Address

  MsgBox MyValue

End Sub

Step 6

Ok, we are done and see what we will get in the message box.

Step 7

We got the result as B5 i.e. cell address of the intersection point of the supplied range.

Like this using the VBA INTERSECT method, we can do many more things.

Example #2

Select the Intersection Cell

In order to select the intersection cell of the supplied range use the below code.

Code:

Sub Intersect_Example2()

  Intersect(Range("B2:B9"), Range("A5:D5")).Select

End Sub

This will select the intersection cell of the supplied range.

Example 2

Example #3

Clear Content of the Intersection Cell: In order to clear the content of the intersection cell of the supplied range uses the below code.

Code:

Sub Intersect_Example2()

  Intersect(Range("B2:B9"), Range("A5:D5")).ClearContents

End Sub

Example #4

Change the Cell Color Background and Font Color of Intersection Cell: In order to change the background color of the intersection cell and the font color of the intersection cell value using the below code.

Code:

Sub Intersect_Example2()

 Intersect(Range("B2:B9"), Range("A5:D5")).Cells.Interior.Color = rgbBlue
 Intersect(Range("B2:B9"), Range("A5:D5")).Cells.Font.Color = rgbAliceBlue

End Sub

Change the Value of the Intersection Cell: Using the Intersect function, we can also change the value of that cell into something else.

Example 2-1

In the above data, the intersect value of the range “B2:B9” & “A5:D5” is cell B5 i.e., marked with blue color. Now by supplying this range to intersect function, we can actually change the value to something else.

The below code will change the value from 29398 to “New Value.”

Code:

Sub Intersect_Example3()

  Intersect(Range("B2:B9"), Range("A5:D5")).Value = "New Value"

End Sub

Run the code above. We will get the word “New Value” in place of 29398.

Example 3

Like this, by using the Intersect function, we can play around with the middle position value of the supplied range.

Things to Remember

  • In excel, to get the intersect value of the range, we need to give space character in between two ranges.
  • By using VBA coding, we can highlight, format, delete or change, and do many other things to the intersection value.
  • If the multiple rows and columns are supplied to the intersect function, then we will get the middle two values.

Recommended Articles

This has been a guide to VBA Intersect. Here we learn how to highlight, format, delete or change, and do many other things to the intersection value using the excel VBA Intersect method with examples and download template. Below are some useful excel articles related to VBA –

  • VBA Comment Block
  • VBA ChDir
  • Subscript Out of Range in VBA
  • Global Variables in VBA
4 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Intersect Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More