Excel VBA Union Method for Range
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
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/