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

Get FREE Advanced Excel Exercises with Solutions!

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.

Sales data of ABC store

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


Applying formula using Union Operator

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.

all months selected after using union operator

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.


=SUM(C5:D10 C6:F6)

Applying formula using Intersection Operator

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.

required months selected after applying intersection operator

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.

Selecting visual basic from developer tab

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

Inserting a module

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

Inserting Application.Union method's VBA

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.

Selecting macro for applying Application.Union method

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

Output of Application.Union Method

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.

Illustration of two tables to join

First, select Visual Basic from the Developer tab.

Selecting visual basic from developer tab

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

Inserting a module

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 Insert code

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

Selecting macro to do union of two tables

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

Output of applying VBA

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.


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

<< Go Back to Excel OperatorsExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Priti Halder holds a BSc degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. She has been a part of the ExcelDemy project for 6 months and during this time, she has written over 30 articles and 5 comments for the platform. Priti is currently employed as an Excel and VBA content developer and provides effective solutions to various Excel-related issues. She is passionate about expanding her knowledge of data analysis and Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF