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
| Name | Requirement | Data Type | Description |
|---|---|---|---|
| Arg1 | Required | Range | There must be a minimum of two Range objects defined. |
| Arg2 | Required | Range | There must be a minimum of two Range objects defined. |
| Arg3- Arg30 | Optional | Variant | A range. |
- 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
🗝️ How Does the Code Work?
Here we have created a subroutine named Unifying_Ranges. Then we activated the Sheet1 worksheet. Later, we applied the Union function to select the B4:B8 and C4:C8 ranges.
- 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: VBA Union Method to Join Range 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
🗝️ How Does the Code Work?
Here, we have created a subroutine named BG_Color_Change. Then, we activated the Sheet2 worksheet. Later, We used the Union function to select the ranges B4:B8 and C4:C8. Finally, we changed the interior color by using Interior.Color command and gave the RGB values for my desired color Cyan. ( 150, 250, 230). According to your wish, you have to input the RGB color code of your desired color.
- 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.
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
🗝️ How Does the Code Work?
Here, we first took a subroutine named Display_Cell_Address. Then We took r and i as Range type variables. After that, we assigned the unified range of B4:B8 and C4:C8 to r. Finally, we display the address of each cell using For loop.
- 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.
