How to use Union and Intersect in Excel VBA?

In this post, you’ll learn how to use Union and Intersect in your Excel spreadsheet using Excel VBA.

Union & Intersect in Excel VBA

To manipulate two or more ranges Excel VBA has two different methods, they are:

• Union
• Intersect.

Union in Excel VBA

The Union method in Excel VBA returns a Range object that represents the union of two or more ranges

Code:

Sub UnionInVBA()

Union(Range("A1:C10"), Range("C4:F8")).Select

End Sub

Using the Union method, you can assign any value or formula to the range.

Code:

Sub UnionInVBA()

Union(Range("A1:C10"), Range("C4:F8")) = 57

End Sub

Wrapping using union

You can wrap a range of cells and you can summarize the value in them.

Code:

Sub UnionInVBA()

Result = Application.WorksheetFunction.Sum(Union(Range("A1:C10"), Range("C4:F8")))

MsgBox Result

End Sub

Intersect in Excel VBA

Intersect method returns only the common cells in two or more ranges.

Code:

Intersect(Range("A1:B7"),Range("B5:C10")).Select