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.
âš¡ Code Breakdown: The code is divided into 2 steps.
- 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.
- 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.
- 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.