VBA Union

What is Excel VBA Union?

In Excel VBA, the Union function combines multiple ranges into a single range. A range in Excel VBA refers to a collection of cells, and the VBA Union function allows you to create a new range that includes all the cells from the specified ranges. It can be useful when operating on multiple non-contiguous ranges simultaneously.

Consider the following example:

VBA Union Intro.jpg

Here, we have assigned two ranges, A1:A5 and C1:C5, to variables Range1 and Range2, respectively. Then, we combined them using VBA Union. The output is shown below:

VBA Union Intro - Output.jpg
Key Takeaways
  1. VBA’s Union function combines multiple non-contiguous ranges into a single range, streamlining range operations.
  2. The function operates exclusively within the same worksheet, ensuring seamless integration of data. The resulting range is a composite of unique cells extracted from the ranges combined using Union.
  3. Union proves advantageous for batch formatting and collective data manipulation across multiple ranges.
  4. Empty ranges are excluded from the union process, eliminating the need for additional checks.

Syntax

The syntax for implementing VBA Union is as shown below:

Set CombinedRange = Union(Range1, Range2, Range3, …)

The components referred to here are:

  • CombinedRange: This new range will contain all the cells from the specified ranges. It’s created by using the Union function.
  • Range1, Range2, Range3, …: These are the individual ranges that you want to combine. You can include as many ranges as needed, separated by commas.

How to use the VBA Union Method to Join Multiple Range?

To create your custom progress bar in VBA, follow the steps below:

Step 1: Go to the Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Developer - Visual Basic
Insert - Module

Consider the table given below:

VBA Union - Use - Table.jpg

Step 2: Create a subroutine to perform the VBA Union function on the three columns shown above, and then mark the values greater than 5000$ in blue font and bold it.

VBA Union - Use - Step 2.jpg

Step 3: Initialize three ranges and one iterative variable for the FOR-loop.

VBA Union - Use - Step 3.jpg

Step 4: Set the three ranges in VBA that need to be unionized.

VBA Union - Use - Step 4.jpg

Step 5: Initialize a FOR-loop with the iterative variable and check if the table values exceed 5000. If so, bold the font and change the color to blue.

VBA Union - Use - Step 5.jpg

Code:

Sub FormatSalesData()
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Cell As Range
Set Range1 = Range(“A2:A10”)
Set Range2 = Range(“C2:C10”)
Set Range3 = Range(“E2:E10”)
For Each Cell In Union(Range1, Range2, Range3)
If IsNumeric(Cell.Value) And Cell.Value > 5000 Then
Cell.Font.Bold = True
Cell.Font.Color = RGB(0, 0, 255) ‘ Blue font color
End If
Next Cell
End Sub

Step 6: Run the program. Get back to the worksheet to view the output.

VBA Union - Use - Step 6.jpg

Now that we know how to use VBA Union, let us see some examples to understand the concept of the VBA Union Function.

Examples

Here are a few examples to show how the VBA Union Range can be used for practical examples in our day-to-day lives.

Example #1

Consider you have a product table. You must highlight the rows with product names starting with “A” and “B.” It can be done by creating a new range and combining the rows where the VBA Left function of either A or B returns True to the range with the help of the VBA Union Range Function.

VBA Union - Example 1.jpg

Step 1: Insert a subroutine into the module to highlight rows.

VBA Union - Example 1 - Step 1.jpg

Step 2: Declare three ranges for the table, an iterative variable range, and another variable to store the rows with the words starting with “A” or “B.”

VBA Union - Example 1 - Step 2.jpg

Step 3:

  • Initialize a FOR-loop for the table.
  • Check if the Left function for either “A” or “B” returns true.
  • Add it to the new range.

If it’s the first value, add the first cell value returning True.

VBA Union - Example 1 - Step 3.jpg

Step 4: If the range is not empty, combine the new cell range with the pre-existing Range.

VBA Union - Example 1 - Step 4.jpg

Step 5: Check whether the Union range is not empty. If true, highlight all the cells in the range as Green.

VBA Union - Example 1 - Step 5.jpg

Code:

Sub HighlightProducts()
Dim DataRange As Range
Dim Cell As Range
Dim UnionRange As Range
For Each Cell In Range(“A2:A20”)
If Left(Cell.Value, 1) = “A” Or Left(Cell.Value, 1) = “B” Then
If UnionRange Is Nothing Then
Set UnionRange = Cell
Else
Set UnionRange = Union(UnionRange, Cell)
End If
End If
Next Cell
If Not UnionRange Is Nothing Then
UnionRange.Interior.Color = RGB(0, 255, 0) ‘ Green
End If
End Sub

Step 6: Run the above code for the table. The output is shown below.

VBA Union - Example 1 - Step 6.jpg

All the Cells with product names starting with “A” and “B” have been successfully highlighted.

Example #2

Suppose you have a table with the Employee data. The company does not need those in the HR department and the Finance Section for a particular presentation. Your job is to return the employee list after clearing the HR and Finance department. It can be done by performing a VBA Union Query on the data.

VBA Union - Example 2.jpg

Step 1: Create a subroutine to delete data from the table.

VBA Union - Example 2 - Step 1.jpg

Step 2: Initialize three ranges, including the table range, the range that needs to be deleted, and the iterative variable.

VBA Union - Example 2 - Step 2.jpg

Step 3: Initialize a FOR loop to move through the table.

VBA Union - Example 2 - Step 3.jpg

As a result of trying to delete all the columns related to “HR” or “Finance”, combine the adjacent column cells in columns B, C, D into a range called UnionRange with

Set UnionRange = Range(“B” & Cell.Row & “,C” & Cell.Row & “,D” & Cell.Row).

Incorporate the values of the adjacent columns if a cell value returns “True” for the Department roles “Finance” and “HR”. For subsequent values, use the VBA Union Query function to concatenate the values.

Step 4: Use the Union function to concatenate the following values that corroborate the conditions set in the previous step.

Example 2 - Step 4.jpg

As mentioned in the previous step, since the range is not empty as a result of adding the first range, use the VBA Union Function to combine the adjacent values of the cell value which returned “True” for the If-Else condition mentioned earlier. Hence, to add ranges to the pre-existing range instead of substituting it, declare it as a Range variable, initially and use the Union Function for the line:

Set UnionRange = Union(UnionRange, Range(“B” & Cell.Row & “,C” & Cell.Row & “,D” & Cell.Row))

Step 5: Check whether the combined range is not empty. If not, use the VBA ClearContents function to delete the values.

VBA Union - Example 2 - Step 5.jpg

Code:

Sub ClearDepartmentData()
Dim DeptRange As Range
Dim Cell As Range
Dim UnionRange As Range
For Each Cell In Range(“A1:A20”)
If Cell.Value = “HR” Or Cell.Value = “Finance” Then
If UnionRange Is Nothing Then
Set UnionRange = Range(“B” & Cell.Row & “,C” & Cell.Row & “,D” & Cell.Row)
Else
Set UnionRange = Union(UnionRange, Range(“B” & Cell.Row & “,C” & Cell.Row & “,D” & Cell.Row))
End If
End If
Next Cell
If Not UnionRange Is Nothing Then
UnionRange.ClearContents
End If
End Sub

Step 6: Run the program. The output will be displayed below:

Example 2 - Step 6.jpg

Error with Union Function

Error in the VBA Union function can appear for various reasons. They are:

Invalid Ranges: Make sure the ranges you pass to the Union function are valid. They should be ranges within the same worksheet and properly defined using the Range object. Verify that the ranges exist and have the correct references.

Non-Contiguous Ranges: The Union function is meant to combine non-contiguous ranges. If you’re trying to combine ranges that are not adjacent to each other, the Union function should work. However, using a single range reference might be more appropriate if you’re dealing with contiguous ranges.

Let us see an example where an Error occurs for an invalid range. An error with the Union function might occur due to improper use. Assume you are trying to combine two ranges, but one of the ranges is invalid or does not exist in the worksheet as shown below:

In the code, consider the snippet:

VBA Union - Error - code.jpg

Here, two ranges are considered from different sheets in this code. We know that VBA Union works only with ranges in the same worksheet. Hence, the unionized range here is regarded as an “Invalid” range.

With no Error Handling, a runtime error occurs as shown below for the given snippet:

VBA Union - Error - combinedrange.jpg
VBA Union - Runtime error.jpg

It can be avoided using Error handling for the above VBA Union Query for the ranges.

Error - Error Handling.jpg

It will avoid triggering the Runtime error in Excel VBA. But, due to unionizing invalid range types, the length of the ‘CombinedRange’ variable will still be 0. It is observed by printing message boxes for the conditions as shown below:

Error - Msgbox.jpg

Using Error handling, it is possible to prevent triggering the failing runtime scenario which might stop the VBA Execution in its tracks.

Consequently, due to the range still being 0, the MsgBox with the message, “Error occurred while combining ranges.” is shown.

Error - Error occured.jpg

Here, the vbExclamation inserts an exclamation icon into the message box.

Code:

Sub UnionFunctionErrorExample()
Dim Range1 As Range
Dim Range2 As Range
Dim CombinedRange As Range
‘This is an invalid range
Set Range1 = Sheets(“Sheet1”).Range(“A1:A5”)
Set Range2 = Sheets(“Sheet2”).Range(“B1:B5”)
On Error Resume Next
Set CombinedRange = Union(Range1, Range2)
On Error GoTo 0
If Not CombinedRange Is Nothing Then
MsgBox “Ranges were successfully combined.”, vbInformation
Else
MsgBox “Error occurred while combining ranges.”, vbExclamation
End If
End Sub

Important Things To Note

  • The Union function combines multiple non-contiguous ranges into a single range.
  • Ranges should be from the same worksheet; the Union function doesn’t work across different worksheets.
  • The combined ranges should be compatible in structure (e.g., rows and columns).
  • The Union function returns a new range that includes all cells from the specified ranges.
  • It’s essential to check if the Union operation was successful by verifying if the returned range is not Nothing.
  • Use proper error handling to deal with potential errors, such as trying to combine incompatible ranges or empty ranges.
  • The Union function is helpful for simultaneously applying actions to multiple ranges, such as formatting or data manipulation.

Frequently Asked Questions (FAQs)

1. What is the difference between Union and Intersect in VBA?

Here are some differences between VBA Union and Intersect.

FAQ 1.jpg

2. Why is VBA Union not working?

• Double-check that the range references are accurate and point to valid cells or ranges.
• Union doesn’t work across different sheets, the ranges should be within the same sheet.
• Ranges must have compatible structures (e.g., same number of rows and columns).
• Contiguous ranges might not work well with Union; Hence, we should use a single range reference instead.
• Union can’t include empty ranges; ensure the ranges you’re trying to combine have data.
• Check if any error occurs during the Union function call. Use proper error handling to identify issues.

3. Can I combine non-contiguous ranges using VBA Union?

Yes, you can combine non-contiguous ranges using VBA Union.

Download Template

This article must be helpful to understand the VBA UNION, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to VBA Union. Here we explain how to use the union method to join multiple ranges with examples and points to remember. You may learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *