In a recent project I’m involved in, I was asked to develop a function that would return a range containing all the cells of 2 ranges with the exclusion of the cells they had in common. In other words, the requirement was to obtain a Union between 2 ranges without the Intersection of the 2 ranges.
Excel VBA does not allow subtracting ranges, e.g. Set rngR3 = rngR1 - rngR2, so I had to develop a function doing this for me. The effort has resulted in a function I named UnionExclusive():
Function UnionExclusive(ByRef r1 As Excel.Range, r2 As Excel.Range) As Excel.Range
'
' This function returns the range of cells that is the Union of both ranges with the
' exclusion of the ranges or cells that they have in common.
'
On Error Resume Next
Dim rngWholeArea As Excel.Range
Dim rngIndividualArea As Excel.Range
Dim rngIntersection As Excel.Range
Dim rngIntersectArea As Excel.Range
Dim rngUnion As Excel.Range
Dim rngSection As Excel.Range
Dim rngResultingRange As Excel.Range
Dim lngWholeTop As Long
Dim lngWholeLeft As Long
Dim lngWholeBottom As Long
Dim lngWholeRight As Long
Dim arrIntersection As Variant
Dim arrWholeArea As Variant
'
' Must be on same sheet, return only first range.
'
If Not r1.Parent Is r2.Parent Then Set UnionExclusive = r1: Exit Function
'
' No overlapping cells, return the union.
'
If Application.Intersect(r1, r2) Is Nothing Then Set UnionExclusive = Application.Union(r1, r2): Exit Function
'
' Range to subtract must be contiguous. If the second range has multiple areas, loop through all the individual areas.
'
If (r2.Areas.Count > 1) _
Then
Set rngResultingRange = r1
For Each rngIndividualArea In r2.Areas
Set rngResultingRange = UnionExclusive(rngResultingRange, rngIndividualArea)
Next rngIndividualArea
Set UnionExclusive = rngResultingRange
Exit Function
End If
'
' Get the overall size of the Union() since Rows/Columns "Count" is based on the first area only.
'
Set rngUnion = Application.Union(r1, r2)
For Each rngIndividualArea In rngUnion.Areas
If (lngWholeTop = 0) Then lngWholeTop = rngIndividualArea.Row Else lngWholeTop = Application.WorksheetFunction.Min(lngWholeTop, rngIndividualArea.Row)
If (lngWholeLeft = 0) Then lngWholeLeft = rngIndividualArea.Column Else lngWholeLeft = Application.WorksheetFunction.Min(lngWholeLeft, rngIndividualArea.Column)
If (lngWholeBottom = 0) Then lngWholeBottom = (rngIndividualArea.Row + rngIndividualArea.Rows.Count - 1) Else lngWholeBottom = Application.WorksheetFunction.Max(lngWholeBottom, (rngIndividualArea.Row + rngIndividualArea.Rows.Count - 1))
If (lngWholeRight = 0) Then lngWholeRight = (rngIndividualArea.Column + rngIndividualArea.Columns.Count - 1) Else lngWholeRight = Application.WorksheetFunction.Max(lngWholeRight, (rngIndividualArea.Column + rngIndividualArea.Columns.Count - 1))
Next rngIndividualArea
arrWholeArea = Array(lngWholeTop, lngWholeLeft, lngWholeBottom, lngWholeRight)
'
' Get the entire area covered by the various areas.
'
Set rngWholeArea = rngUnion.Parent.Range(rngUnion.Parent.Cells(lngWholeTop, lngWholeLeft), rngUnion.Parent.Cells(lngWholeBottom, lngWholeRight))
'
' Get intersection, this is or are the area(s) to remove.
'
Set rngIntersection = Application.Intersect(r1, r2)
For Each rngIntersectArea In rngIntersection.Areas
arrIntersection = Array(rngIntersectArea.Row, _
rngIntersectArea.Column, _
rngIntersectArea.Row + rngIntersectArea.Rows.Count - 1, _
rngIntersectArea.Column + rngIntersectArea.Columns.Count - 1)
'
' Get the difference. This is the whole area above, left, below and right of the intersection.
' Identify if there is anything above the intersection.
'
Set rngSection = Nothing
If (arrWholeArea(0) < arrIntersection(0)) _
Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrWholeArea(1)), _
rngWholeArea.Parent.Cells(arrIntersection(0) - 1, arrWholeArea(3))), _
rngUnion)
If Not rngSection Is Nothing _
Then
If rngResultingRange Is Nothing _
Then Set rngResultingRange = rngSection _
Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
End If
'
' Identify if there is anything left of the intersection.
'
Set rngSection = Nothing
If arrWholeArea(1) > arrIntersection(1) _ Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrWholeArea(1)), _ rngWholeArea.Parent.Cells(arrWholeArea(2), arrIntersection(1) - 1)), _ rngUnion) If Not rngSection Is Nothing _ Then If rngResultingRange Is Nothing _ Then Set rngResultingRange = rngSection _ Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection) End If ' ' Identify if there is anything right of the intersection. ' Set rngSection = Nothing If arrWholeArea(3) &amp;gt; arrIntersection(3) _
Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrWholeArea(0), arrIntersection(3) + 1), _
rngWholeArea.Parent.Cells(arrWholeArea(2), arrWholeArea(3))), _
rngUnion)
If Not rngSection Is Nothing _
Then
If rngResultingRange Is Nothing _
Then Set rngResultingRange = rngSection _
Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
End If
'
' Identify if there is anything below the intersection.
'
Set rngSection = Nothing
If arrWholeArea(2) > arrIntersection(2) _
Then Set rngSection = Application.Intersect(rngWholeArea.Parent.Range(rngWholeArea.Parent.Cells(arrIntersection(2) + 1, arrWholeArea(1)), _
rngWholeArea.Parent.Cells(arrWholeArea(2), arrWholeArea(3))), _
rngUnion)
If Not rngSection Is Nothing _
Then
If rngResultingRange Is Nothing _
Then Set rngResultingRange = rngSection _
Else Set rngResultingRange = Application.Union(rngResultingRange, rngSection)
End If
Set rngUnion = rngResultingRange
Set rngResultingRange = Nothing
Next rngIntersectArea
'
' Return the result. This is the area "around" the intersection.
'
Set UnionExclusive = rngUnion
End Function
The function takes 2 parameters, the first is the range to start with, the second is the range that you want to union-exclusive with the first. The following code snippet shows the effect using colour coding:
Sub Test() Dim r As Excel.Range ActiveSheet.Cells.Clear Set r = UnionExclusive([A3:C11], [B2:B16]) r.Interior.ColorIndex = 6 ' Yellow End Sub
The function uses only Application.Union() and Application.Intersect() and avoids at all time looping on cells within a range or area of a range, as doing this terribly affects performance when the ranges used are (very) large (e.g. whole columns).
The trick consists in looking at the intersection and the whole area occupied by the union of the ranges, i.e., the lowest and the highest row and column, while working with the bare union of both ranges. The whole area is needed for including any cell the union covers, the original union is needed for including only cells actually part of the union.
The function will look at any area above, left, right and below the intersection. From any of the mentioned areas, the function only returns the intersection and finally unions them for obtaining the range to return.
Let’s take the following example: you want to union-exclusive [A3:C10] with [B1:B12]. I have highlighted the cells that represent (top, left) and (bottom, right) of the whole area covered by the union.
- The union consists of a range made out of 2 areas
[A3:C10,B1:B12] - The intersection is
[B3:B10] - The whole area occupied by the union is
[A1:C12]thus from row 1 to row 12 and column 1 to column 3 - The area above the intersection is
Application.Intersect([A1:C2],[B1:B2]) = [B1:B2] - The area left of the intersection is
Application.Intersect([A1:A12],[A3:A10]) = [A3:A10] - The area right of the intersection is
Application.Intersect([C1:C12],[C3:C10]) = [C3:C10] - The area below the intersection is
Application.Intersect([A11:C12],[B11:B12]) = [B11:B12] - The resulting range is
Application.Union([B1:B2],[A3:A10],[C3:C10],[B11:B12])with 4 individual areas
Both function parameters allows for ranges with multiple areas, and the function also captures multiple intersections between areas of one range and the individual areas of the other range.
When the second range is indeed formed by multiple areas then the resulting range will be the sequential application of each area of the second range:
Sub Test() Dim r As Excel.Range ActiveSheet.Cells.Clear Set r = UnionExclusive([F2:J14], [G2:G6,G9:G11,I2:I11,J4:J5,F12:J13]) ' 5 areas in one run r.Interior.ColorIndex = 5 ' Blue
is the same as:
Sub Test() Dim r As Excel.Range ActiveSheet.Cells.Clear Set r = UnionExclusive([F17:J29], [G17:G21]) ' First area Set r = UnionExclusive(r, [G24:G26]) ' Second area Set r = UnionExclusive(r, [I17:I26]) ' Third area Set r = UnionExclusive(r, [J19:J20]) ' Fourth area Set r = UnionExclusive(r, [F27:J28]) ' Fifth area r.Interior.ColorIndex = 7 ' Magenta End Sub
Applying union-exclusion sequentially has one drawback: a subsequent area may restore cells that were excluded by a previous operation. For instance the following code creates a cross because cells B3 and B4 are restored by the second area:
Sub Test() Dim r As Excel.Range ActiveSheet.Cells.Clear Set r = UnionExclusive([A1:C6], [B1:B6,A3:C4]) r.Interior.ColorIndex = 4 ' Green End Sub
If time allows then I will try to resolve this issue and return a range that is the true union-exclusion of both areas.
Enjoy.






