VBA Union

Updated June 12, 2023

VBA Union

As the word itself suggests, union means joining one or more things. In VBA Union means joining two or more ranges together. This function is similar to the range function in excel. This is the most common situation in our work when we need to combine one or more ranges with each other. Union function comes in very handy in those situations.

As explained above, VBA Union functions combine one or more ranges. We can use this function to combine ranges with some common criteria. For example, if our data has a value less than a specific value, we can use this function to combine those ranges and highlight them.

Syntax of VBA Union in Excel

The syntax for the Union function is as follows:

So, for example, if we want to combine a range A1: A5 and B1: B5, we will use the following formula,

Union (Range (“A1:A5”), Range (“B1:B5”)

We can do much more with this function, and we will see through various examples of how to use this function in VBA.

First, let us ensure we have a developer’s tab enabled from the files tab in the options section to start using VBA in Excel.

How to Use VBA Union Function in Excel?

We will learn how to use a VBA Union function with a few examples in excel.

Example #1 – VBA Union

In the first example, let us try to select two ranges together. Let us select A1:A5 and B1:B5 range together in this example.

Follow the below steps to use the VBA Union function in Excel:

Step 1: We need to open the VB editor from visual basic, which is in the developer’s tab.

Step 2: Once we are in VB Editor, insert a new module from the insert section. The module we have inserted, double-click on it so that we can start writing code.

Step 3: Once we are in the code window, name the macro as follows,

Code:

Sub sample()

End Sub

Step 4: Since we will work with sheet 1, we must activate it first to use its properties.

Code:

Sub sample()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now, we will use the union function to combine the two ranges we have discussed above with the following code.

Code:

Sub sample()

Worksheets("Sheet1").Activate
Application.Union(Range("A1:A5"), Range("B1:B5")).Select

End Sub

Step 6: Once we execute the code above, we can see in sheet 1 that those two ranges are in our selection. Press F5 or do it manually from the run button to see the result.

In the above example, we have only selected the two ranges, but we can do much more which we will learn in the next examples.

Example #2 – VBA Union

Now in this example, let us select two ranges as above together and change their interior color. We can change the format or change values once we combine and select the ranges together.

Step 1: Go to Insert Menu and click on the module

Step 2: Declare a name for the subfunction for the second example,

Code:

Sub Sample1()

End Sub

Step 3: Now, let us activate sheet 2 first since we will use the properties of sheet 2 in this example.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate

End Sub

Step 4: Combine two ranges, A1:B5 and C1:D5, with the range function and change the interior color to a dark red by the following code.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate
Application.Union(Range("A1:B5"), Range("C1:D5")).Interior.Color = 255

End Sub

Step 5: Execute the above and see the result in sheet 2 as follows,

After combining them, we have changed the color of the ranges as we can see that they are still in selection.

Example #3 – VBA Union

Now let’s use the union function to display the address after combining ranges. We will combine range A1:C4 and E1:F4 and display the address in the Immediate window. An immediate window is just below our code window, or we can press CTRL + G to bring it up.

Step 1:  Go to Insert Menu and click on the module,

Step 2: Name the macro name for this third example.

Code:

Sub Sample2()

End Sub

Step 3: Declare two variables as a range in the next step as follows.

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range

End Sub

Step 4: Now set an rng1 variable as the union of the range A1: C4 and E1: F4 as follows,

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range
    Set rng1 = Union(Range("A1:C4"), Range("E1:F4"))
End Sub

Step 5: Now use for loop to bring the address of these cells from the combined ranges by the following code,

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range
    Set rng1 = Union(Range("A1:C4"), Range("E1:F4"))
    For Each item In rng1
        Debug.Print item.Address
    Next item
End Sub

Step 6: Once we run the above code, we can see the result in the immediate window as follows,

Application of VBA Union

The following syntax uses VBA union:

Expression.Union(range1, range2,…..)

Here we can use as many ranges as we require.

Things to Remember

There are a few things that we need to remember about the union in VBA:

• The union is used to combine two or more ranges together.
• The ranges we give to the function must exist to avoid an error.
• Instead of Application. The union we can simply use the union as we are working in Excel itself.

Recommended Articles

This is a guide to VBA Union. Here we discuss how to use Excel VBA Union Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

1. VBA Copy Paste
2. VBA XML
3. Excel VBA Macro
4. VBA Login