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:

The three ranges that we combined into one range now all contain the **RANDBETWEEN** formula.

**Note**: You can find the complete documentation for the VBA **Union **method here.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

How to Use Intersect in VBA

How to Count Number of Used Columns in VBA

How to Count Number of Sheets in Workbook in VBA