VBA Union Method to Join Range in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The VBA Union method helps to put several ranges into one single range. You can also find and sort a wide range of data into one single range based on specific criteria such as putting all negative or odd numbers of a dataset. However, this article will discuss how to use VBA Union to join the range in Excel.

Table of Contents hide

Download Practice Workbook

3 Examples of Using VBA Union Method to Join Range in Excel

1. Combining Two or Multiple Sets of Ranges Together

2. Selecting Subsets of a Given Range

3. Storing Range of Cells in a Specific Variable

Things to Remember

Practice Section

Conclusion

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

VBA Union Range.xlsm

3 Examples of Using VBA Union Method to Join Range in Excel

Let’s assume we have a dataset, namely “Employee List of Kenedy Corporation”. You can use any dataset suitable for you.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Combining Two or Multiple Sets of Ranges Together

Using ranges within a worksheet and referencing them is a crucial part of VBA coding. The methods of the Range Object can be used to refer to or access a worksheet range. A Range Object can be a single cell, or two or more cells combined together. Considering today’s topic, we will select two sets of ranges and combine them using VBA code.

📌 Steps:

To begin with, press Alt + F11 to open your Microsoft Visual Basic.
Then press Insert > Module to open a blank module.

Now, write the following VBA code in your Module1.

Sub CombiningTwoSets()
Dim ws As Worksheet
Dim IniRange As Range, IniRange2 As Range, cell As Range, _
FinalRange1 As Range, UniRng As Range, rngIntersect As Range, FinalRange2 As Range
Dim count As Integer
Set ws = Worksheets("Sheet1")
Set IniRange = ws.Range("C5:C12")
Set IniRange2 = ws.Range("B7:D9")
ws.Cells.Interior.ColorIndex = xlNone
Union(IniRange, IniRange2).Interior.Color = vbGreen
If Intersect(IniRange, IniRange2) Is Nothing Then
MsgBox "Ranges do not intersect"
Else
Intersect(IniRange, IniRange2).Interior.Color = vbGreen
End If
count = 0
For Each cell In IniRange
If Intersect(cell, IniRange2) Is Nothing Then
count = count + 1
If count = 1 Then
Set FinalRange1 = cell
Else
Set FinalRange1 = Union(FinalRange1, cell)
End If
End If
Next
FinalRange1.Interior.Color = vbGreen
Set UniRng = Union(IniRange, IniRange2)
If Not Intersect(IniRange, IniRange2) Is Nothing Then
Set rngIntersect = Intersect(IniRange, IniRange2)
For Each cell In UniRng
If Intersect(cell, rngIntersect) Is Nothing Then
If FinalRange2 Is Nothing Then
Set FinalRange2 = cell
Else
Set FinalRange2 = Union(FinalRange2, cell)
End If
End If
Next
FinalRange2.Interior.Color = vbGreen
Else
UniRng.Interior.Color = vbGreen
End If
End Sub

Now we will demonstrate to you how the given code works. Look at the code breakdown given below to get a general idea.

⚡ Code Breakdown:

The code is divided into 2 steps.

In the first portion, some variables have been assigned to initiate the code such as iniRange, iniRange2, cell and so on. Subsequently, we activate Sheet1 as our worksheet.
Apart from the VBA Union method, the VBA Intersect method is also used in the second and third portions of the given code. The reason behind this is unlike the mathematical union operator, the VBA union method count twice the overlapping region of the C5:C12 & B7:D9 So, we need to remove one of the layers by using the Intersection function. Interior.color function sets a background color palette in the C5:C12 & B7:D9 range combined by putting the desired range of data in FinalRange2 using the Union Function.

Press F5 to run your VBA Code.
Close your VBA window.
Now see the output as depicted below.

2. Selecting Subsets of a Given Range

Another exciting aspect of the VBA Union method is that it allows you to select a set of data from a specific range based on common criteria, like selecting all negative numbers. Likewise, we will now develop a VBA code to find negative Employee Ratings from our selected dataset, as we provided earlier.

📌 Steps:

As we mentioned previously, press Alt + F11 to open your Microsoft Visual Basic.
Then press Insert > Module to open a blank module.

Now, write the following VBA code in your Module2.

Sub SelectingSubset()
    Dim RangePos As Range
    Dim RangeNeg As Range
    Dim RangeZero As Range
    Dim FinalRow As Long
    Dim i As Long   
    FinalRow = Range("D" & Rows.count).End(xlUp).Row
    For i = 1 To FinalRow
        If IsNumeric(Range("D" & i)) Then
            If Range("D" & i) > 0 Then
                If RangePos Is Nothing Then
                     Set RangePos = Range("D" & i)
                Else
                    Set RangePos = Union(Range("D" & i), RangePos)
                End If
            ElseIf Range("D" & i) < 0 Then
                If RangeNeg Is Nothing Then
                     Set RangeNeg = Range("D" & i)
                Else
                    Set RangeNeg = Union(Range("D" & i), RangeNeg)
                End If
            Else
                If RangeZero Is Nothing Then
                     Set RangeZero = Range("D" & i)
                Else
                    Set RangeZero = Union(Range("D" & i), RangeZero)
                End If
            End If
        End If
    Next i
    RangePos.Select
    RangeNeg.Font.Color = vbRed
    RangeZero.Font.Italic = True
End Sub

Now we will discuss how this code works in your VBA module.

⚡ Code Breakdown:

The code is divided into 3 steps.

In the first portion, there are some variables that we need to use to run the code.
In the second portion, there is an if-else loop that will search the numerical value in column D. If the cell value is greater than zero nothing will take into account but put the cell value in the RangeNeg variable otherwise.
In the third section of the given code, the cell containing a negative value will mark with Red color so that the value can be separated easily from the others.

Press F5 to run your VBA Code.
Close your VBA window.
Now see our output of the code as given below.

3. Storing Range of Cells in a Specific Variable

The third method of VBA Union that we are now going to talk on how to store a range of data in a specific variable. You can assign this variable to a specific task later on, but for now, we will learn how to define it and assign a range of data to it.

📌 Steps:

Select the Developer tab from Menu Bar and the Visual Basic feature later.

Then press Insert > Module to open a blank module.

In the appeared Module3, write the following code as provided below.

Sub StoringRangeInVariable()
  Dim Range1 As Range
  Dim Range2 As Range
  Set Range1 = Range("B6:B11")
  Set Range2 = Range("D6:D11")
  Union(Range1, Range2).Interior.Color = vbGreen
End Sub

⚡ Code Breakdown:

Now, we will explain how the given VBA code works. The code is divided into 2 steps.

In the first portion, there are two variables we assign named Range1 and Range2 which contain B6:B11 and D6:D11 cell values respectively.
In the second portion, Union(Range1, Range2).Interior.Color = vbGreen returns those two variables and fills the cells with Green color to make them discernible.

Press F5 to run your VBA Code.
Close your VBA window.
Subsequently, see the output as given below.

Things to Remember

One thing you should keep in mind while writing and running the code in the VBA module is that if your Visual Basic window has more than one code in a different module window, then you have to run your code separately. You might run the wrong code in the wrong sheet otherwise.
As from the image attached below, we have 3 different codes in 3 different module boxes. Firstly, save all of those codes by selecting the Save icon highlighted by a rectangular box in the given picture. You can also do the same by applying the CTRL+S shortcut key.

Then close your VBA window and go to Developer > Macro from the Menu Bar.
Thus, Run your code in a specific sheet accordingly.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Conclusion

In this article, we have discussed how to use VBA union to join range in Excel with three examples. Before applying those codes make sure those codes align with your interest and don’t forget to do necessary changes where applicable. Further, if you have any queries, feel free to comment below and we will get back to you soon.