Excel VBA Union Method

Excel VBA Union Method for Range

vba_union

Union Method returns a Range which is combined from two or more Range. In the above diagram, Union A and B returns area A plus B, where the intersection area only counts once.

The first time I used Union was that I needed to do several formatting for several columnS but they were not side by side. For example, I need to change color for column A, column C and column Z,  the best way to do is to combine all three columns  as a new Range, and then apply coloring on the combined Range. When you have several formatting to do on those columns, you will see you can save a lot of codes using Union Method.

Everytime I learn a learn SQL Union, I must ask a question: is that a Union or Union All? There are two kinds of Union, one is “Union” and the other is “Union All”, the latter returns area A plus area B plus intersection Range for two times and therefore duplicate records would return. The Union Method in Excel does not have the Union All concept, Range would not have any effect whether they are duplicated or not, you can just think of the Excel Union Method as normal Union.

Intersection is a twin of Union, people usually put two things together to discuss. You can also read the Intersection Method in the below link

Excel VBA Intersect Method

Syntax of Union Method

Application.Union(Range1,Range2,…)

Note that you need at least two Range for Union Method to work, the code will fail if you only input one Range.

Example of Union Method

The below code combine column A, column B and column Z as “unionRange”, and then apply color formatting on “unionRange”

Public Sub unionRng()
     Set Range1 = Range("A:A")
     Set Range2 = Range("C:C")
     Set Range3 = Range("Z:Z")
     Set unionRange = Union(Range1, Range2, Range3)
     unionRange.Interior.Color = vbRed
 End Sub

Alternative of Union Method

You can also set multiple Range instead of using Union. The difference is that the required parameter is a Text instead of a Range.

Set multipleRange = Range("A:B,D:D,E:E")

You can define a Named Range in the worksheet first, and then use the name in VBA

Set multipleRange = Range("Range1, Range2, Range3")

It does not matter which method to use, it really depends on how your original source looks like.

Outbound Reference

http://msdn.microsoft.com/en-us/library/office/ff834621%28v=office.15%29.aspx

http://excel-macro.tutorialhorizon.com/vba-excel-refer-to-multiple-ranges-union-method/

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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