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

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.

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.

• 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.

• 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

• 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.

Download Practice Workbook

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

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 make necessary changes where applicable. Further, if you have any queries, feel free to comment below and we will get back to you soon.