Excel Union (Using Operator, Application.Union Method in VBA)

Excel’s union operation is a potent tool that lets you combine data from various ranges into a single object. Utilizing the Union operator and the Application.Union methods are two widely used approaches to performing union operations. Each strategy has advantages and can be applied depending on the requirements and level of programming experience with VBA. You can effectively work with data in Excel by learning these methods. So, let’s explore Excel Union’s knowledge base.

Download Practice Workbook

You can download the practice workbook from here.

Excel Operator for Union and Intersect

In Microsoft Excel, the operator for Union is Comma and the space for Intersection. Union and intersection operators work in SUM, COUNT, COUNTA, SMALL, LARGE, MIN, MAX, AVERAGE, etc. functions.

Let us look into an example of how this operator works in Excel.

Here, we have sales data for ABC company. We want to calculate the total sales for all four months at once. We will apply the union operator in the SUM function to do that.

First, select the cell where you want the sum and insert the formula below, and you will have the total sales for four months.

In this formula, you can see a comma that works as a union operator between ranges. After inserting the formula, you will see that sales data for all four months are selected.

Moreover, you will notice that this operator adds up the range but does not merge them. Similarly, the intersection operator, a space, adds up the common ranges from selected ranges. Now let us see an example of the intersection operator.

Insert the following formula in a cell to calculate the sum of sales of January and February for Mouse.

Here, range C5:D10 contains the sales data for January and February, whereas C6:F6 contains the sales for Mouse of four months. So, the intersection operator (space) selects the range common in both ranges and gives the sales for the product Mouse in January and February.

Read More: Do Union of Two Columns

Overview of Excel VBA Application.Union Method

This method will show you how to apply Application.Union method in Excel VBA to do the union of cells.

First, select Visual Basic from the Developer tab.

After that, select Module from the Insert tab in the Microsoft Visual Basic Applications tab.

Next, insert the following code in the Module.

Sub UnionCells()
Set Rng = Application.Union(Range("D5:D10"), Range("F5:F10"))
Range("C12") = WorksheetFunction.Sum(Rng)
End Sub

Here, Application.Union(Range(“D5:D10”), Range(“F5:F10”)) part selects the ranges D5;D10 and F5:F10 and sets it in Rng variable. Then, this part Range(“C12”) = WorksheetFunction.Sum(Rng) returns the sum of the selected cell in cell C12.

At this point, to run the code, press Alt+F8, select UnionCells macro, and press Run.

Lastly, you will see the sum of the ranges in destination cell.

Create Union of Two Tables Using Excel VBA

This method will demonstrate how to create a union of two tables using Excel VBA.

Here we will merge the table containing February, March, and April sales with the January sales.

First, select Visual Basic from the Developer tab.

After that, select Module from the Insert tab in Microsoft Visual Basic Applications tab.

Next, insert the code in the Module.

Sub MergeTable()
Worksheets("Union of Two Tables").Range("C12:E18").Copy
Worksheets("Union of Two Tables").Range("D4:F10").PasteSpecial
End Sub 

To run the code, press Alt+F8, select MergeTable macro, and press Run.

Lastly, we will have the two tables merged into the destined range.

Read More: Create Union of Two Tables

Things to Remember

• Excel union and intersection operators work in SUM, COUNT, COUNTA, SMALL, and LARGE functions.
• Excel union or intersection operator does not merge the ranges, only selects them.
• While applying macro, ensure you have the Developer tab. If you do not have this tab, go to the File tab and click Options. Then from the Excel Options, go to Customize the Ribbon and put a check on the Developer option.

Conclusion

The Union operator, Application.Union method and VBA are just a few of Excel’s many tools for combining data ranges. These tools offer versatility and efficiency when utilizing multiple ranges or datasets within a worksheet. The Union operator makes it simple for users to combine multiple ranges into a single range, whereas the Application.Union method offers more control and flexibility. Overall, these tools allow users to improve productivity and derive deeper insights from their Excel workbooks.

Frequently Asked Questions

1. What is the union character in Excel?

Answer: The Union Symbol looks like the alphabet “U,” and the Alt Code for inserting it is Alt + 8746.

2. How do you do a union in Excel?

Answer: In MS Excel, the comma is referred to as the UNION operator (e.g., A1:A9,F2:G4).

3. What is the union intersection formula in Excel?

Answer: In MS Excel, the space is used as the INTERSECTION operator (e.g., A1:A9 F2:G4).

Excel Union: Knowledge Hub

• Use VBA Union Function
• Perform Union Query
• Make Union of Two Sheets

<< Go Back to Excel Operators | Excel Formulas | Learn Excel