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

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

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

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.

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

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

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

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

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

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

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

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

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.

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.

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.