Table of Contents
The Union function in VBA allows users to combine multiple ranges or cells into one range. This can be useful for organizing and manipulating data in a spreadsheet or within a VBA program. For example, if a user has data in two separate ranges, they can use the Union function to merge those ranges into one for easier analysis or processing. This function can be implemented by first selecting the ranges to be combined and then using the Union function to merge them together. The resulting range can then be used for various operations such as sorting, filtering, or performing calculations.
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:
The three ranges that we combined into one range now all contain the RANDBETWEEN formula.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: