Table of Contents
Are you looking for an easy way to combine data from different sources in Microsoft Excel? Look no further than the powerful Union function available in Visual Basic for Applications (VBA). In this article, we’ll show you how to use the Union function in VBA with an example. You’ll learn how to combine multiple data ranges into a single result set and how to add criteria to further refine the results. Let’s get started!
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.