How to Use Union in VBA (With Example)

You can use the Union method in VBA to combine multiple ranges into one range.

Here is one common way to use this method in practice:

Sub UseUnion()

Set UnionRange = Application.Union(Range("A1:A10"), Range("C1:C10"))
UnionRange.Formula = "=RANDBETWEEN(1, 100)"

End Sub

This particular macro combines the range A1:A10 and C1:C10 into one range and then assigns the Excel formula =RANDBETWEEN(1, 100) to each value in this combined range, which produces a random value between 1 and 100.

The following example shows how to use the Union method in VBA in practice.

Example: How to Use Union in VBA to Combine Ranges

We can define the following macro in VBA to use the Union method to combine the ranges A1:A10 and C1:C10:

Sub UseUnion()

Set UnionRange = Application.Union(Range("A1:A10"), Range("C1:C10"))
UnionRange.Formula = "=RANDBETWEEN(1, 100)"

End Sub

When we run this macro, we receive the following output:

Each cell in the ranges A1:A10 and C1:C10 now contains the formula =RANDBETWEEN(1, 100), which returns a random integer between 1 and 100.

Also note that we can provide more than two ranges to the Union method.

For example, we could create the following macro that uses the Union method to combine three different ranges and then insert a formula into the cell of each range:

Sub UseUnion()

Set UnionRange = Application.Union(Range("A1:A10"), Range("C1:C10"), Range("D1:D5"))
UnionRange.Formula = "=RANDBETWEEN(1, 100)"

End Sub

When we run this macro, we receive the following output: