VBA Union

Excel VBA Union

Union in VBA is similar to the union in other programming languages, in VBA we use union to combine two or more than two ranges to perform different sets of operations on them, the statement used for this is itself union and it is called as union method, for example, union(Range(B2:C7),Range(D2:E7)).select and this method will select the cells.

Union method performs the task of creating a union of two or more ranges and returns the result as a RANGE object. This works exactly the same as the below example with VBA RANGE objectVBA RANGE ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.

VBA Union

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Union (wallstreetmojo.com)

Syntax

Now take a look at the syntax of the UNION method.

VBA Union formula 1

We need to supply a minimum of 2 ranges.

  • Arg 1: This is the first range of cells we need to create the union of.
  • Arg 2: This is the second range of cells we need to create a union of.

First two parameters are mandatory, after mentioning two range of cells, then all the other arguments become optional.

When the data is scattered in pieces in cells, we need to combine together all the data range to one to perform a common task. We can create a union of scattered range to one to perform a similar task for all the union ranges.

To select multiple ranges of cells, we can usually use RANGE object. For example, if we want to select the range of cells from A1 to B5 and from B3 to D5, we can write the VBA codeWrite The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more like below.

Code:

Sub Union_Example1()

  Union(Range("A1:B5"), Range("B3:D5")).Select

End Sub

This would select the range of cells like the below image.

VBA Union Example 1

As we can see in the above image first range is selected from A1 to B5, and the second range is selected from B3 to D5.

This is the common technique we all have used while coding. However, this is not the only method we have in coding in VBA; we can also use one more method called “union” to create a union of two or more ranges.

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

You can download this VBA Union Excel Template here – VBA Union Excel Template

Example #1

Let’s perform the same take as we did in the above example but this time by using the UNION method.

Step 1 – Open UNION function in the subprocedure.

Code:

Sub Union_Example1()

  Union(

End Sub
VBA Union Example 1-1

Step 2 – Mention the first range of cells using RANGE object. In this case, I am mentioning the first range of cells as A1 to B5.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"),

End Sub
VBA Union Example 1-2

Step 3 – Now mention the second range of cells using RANGE object, in this case, I am mentioning the range of cells as B3 to D5.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"),Range("B3:B5"))

End Sub
VBA Union Example 1-3

Step 4 – After creating the union of these range of cells, we need to decide what we need to do with this union range of cells. Put dot (.) to see the IntelliSense list.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"),Range("B3:B5")).

End Sub
VBA Union Example 1-4

Step 5 – We can see all the available properties and methods of these ranges.

For this example, I will change the Interior colour of union cells. For this I first I need to select the Interior property.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"), Range("B3:B5")).Interior

End Sub
VBA Union Example 1-5

Step 6 – With interior property, we can do many things, but since we need to change the colour of the union cells, I will select Color property.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"), Range("B3:B5")).Interior.Color

End Sub
Example 1-6

Step 7 – Now, we need to set the colour property. I will use a built-in colour index property as vbGreen.

Code:

Sub Union_Example1()

  Union(Range("A1:A5"), Range("B3:B5")).Interior.Color = vbGreen

End Sub
Example 1-7

Step 8 – Now if I run the code colours of the union cells will be changed to Green colour.

Example 1-8

Like this using Union method, we can create unison of two or more range of cells.

Example #2 – Use Variables to Store Range of Cells

All most all the coders use variables to store the reference of the range of cells. For example, look at the below code.

Code:

Sub Union_Example2()

  Dim Rng1 As Range
  Dim Rng2 As Range

  Set Rng1 = Range("A1:B5")
  Set Rng2 = Range("B3:D5")

  Union(Rng1, Rng2).Interior.Color = vbGreen

End Sub

First, I have declared two variables as Range.

Dim Rng1 As Range

Dim Rng2 As Range

Then I have set the reference for these two variables.

Set Rng1 = Range(“A1:B5”)

Set Rng2 = Range(“B3:D5”)

Now variable rng1 holds the reference of Range(“A1:B5”) and the second variable rng2 holds the reference of Range(“B3:D5”).

Then I have applied UNION function to change the interior colour of these range of cells.

This also works exactly the same as the previous one, but using variable makes the code very flexible to use.

Error with Union Function

As I told all the references should be mandatory for the UNION method. For example, look at the below code.

Code:

Sub Union_Example3()

  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim Rng3 As Range

  Set Rng1 = Range("A1:B5")
  Set Rng2 = Range("B3:D5")

  Union(Rng1, Rng2, Rng3).Interior.Color = vbGreen

End Sub

This is similar to the previous, but here I have declared one more variable as Range.

Dim Rng3 As Range

But I have not set the reference to this variable. Rather I just supplied the variable to the UNION function.

Union(Rng1, Rng2, Rng3).Interior.Color = vbGreen

If I run this code, we will get the error like the below.

Error with Union

This is because whatever the variable we supply to the argument should hold some reference of the cells in the worksheet we are working on.

Recommended Articles

This has been a guide to VBA Union Method. Here we discuss how to join multiple ranges using excel VBA Union along with examples and download excel template. You may also have a look at other articles related to Excel VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>