How to Use VBA Union Function in Excel (3 Easy Examples)

In this article, I will show you how to use the VBA Union function in Excel. Among many other VBA functions, Union is a very useful one when it comes to unifying multiple ranges in a worksheet. Here, we will first see the syntax of the Function and then give several examples showing different kinds of applications. So let’s get started.

VBA Union Function Overview

• Description

VBA Union function unifies multiple ranges into a single one in a worksheet.

• Syntax:

Union (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

• Arguments

• Return Value

This function returns a Range.

In this section, we will demonstrate 3 examples in order to illustrate the wide range of applications of the VBA Union function. So, let’s see the examples one by one.

1. Selection of Two Distinct Ranges Using VBA Union Function

In the first example, we will see a fundamental task of combining two distinct ranges into one using the VBA Union function. Here, we have a dataset containing the prices (C5:C8) of some grocery items(B5:B8).

We want to select the two ranges B5:B8 & C5:C8 by using the VBA Union function. To know how we can do that, follow the steps below.

Steps:

• First, we need to open the Visual Basic window and create a new module to write code. To open the Visual Basic window click on Alt+F11.

• Now from the window, select Insert > Module to insert a new module.

• As a result, a new module will open up. Now, write down the following code in the module.

Sub Unifying_Ranges()
Worksheets("Sheet1").Activate
Application.Union(Range("B4:B8"), Range("C4:C8")).Select
End Sub

• Now, run the code by pressing F5. Consequently, you will see that the two ranges have been selected in the worksheet.

In this way, we can select multiple ranges at a time by using the VBA Union function. Later, we can do any operation on the selected multiple ranges according to our wish.

Read More: How to Perform Union Query in Excel

2. Using VBA Union to Change the Background Color of Two Ranges

In the 1st example, we have seen how to select multiple ranges using the VBA Union function. In this example, we will take a step forward by changing the background color of the selected ranges. To do that, follow the steps below.

Steps:

• First, open a new module in the Visual Basic window by following the procedure described in method 1.
• Now, write down the following code in the window.

Sub BG_Color_Change()
Worksheets("Sheet2").Activate
Application.Union(Range("B4:B8"), Range("C4:C8")).Interior.Color = RGB(150, 250, 230)
End Sub

• Now, if you run the code, you will see that the color of your selected ranges has been changed to your desired color.

This is how we can use the Excel VBA Union function to change the background color of selected ranges.

Read More: How to Do Union of Two Columns in Excel

3. Displaying the Address of Combined Ranges in the Immediate Window

Now, in this example, we will learn how to display the address of the cells from combined ranges in the Immediate window. To do this, follow the steps below.

Steps:

• Open a new module in Visual Basic Window by following the process described in example 1.
• Now, write down the following code in the new module window.

Sub Display_Cell_Address()
Dim r As Range
    Dim i As Range
    Set r = Union(Range("B4:B8"), Range("C4:C8"))
    For Each i In r
        Debug.Print i.Address
    Next i
End Sub

• Now, if we run the code, we will see the addresses of those cells in the unified range displayed in the Immediate window.

• If you don’t see the Immediate window, press Ctrl+G to open the window and then run the code again.

Things to Remember

• Remember that the VBA Union function is not like the mathematical Union Operator. Hence, if you try to unify two overlapping ranges (eg: B4:C10 & C5:D10) using the VBA Union function, the overlapped cells(C5, C6…) will appear twice in the unified range, unlike the mathematical Union Operator where there will be no duplicate cells.
• The VBA Union function can’t provide unification of ranges that don’t already exist. As a result, It will show an error.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

That is the end of this article regarding the Excel VBA Union function. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

Related Articles