Studies show that good grammar leads to more promotions and higher salaries. Write with Grammarly

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
How to use Union and Intersect in Excel VBA?

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
How to use Union and Intersect in Excel VBA?

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
How to use Union and Intersect in Excel VBA?