CODEWITHSUNDEEP
vbaexcelunion
hil
hil

Reputation: 161

Excel vba Union method duplicates cells that overlap

I expected the following macro to display 6 but it displays 8. I understand that this is because Union duplicates cells that overlap as do cells b1:b2 in my example:

Sub a()
Dim myRange As Range
Set myRange = Application.Union(Range("a1:b2"), Range("b1:b4"))
MsgBox myRange.Count
End Sub

I found this solution to the problem but I'm interested to know why this is the way Union works and if there is any other way to get around this besides writing a new function as suggested by the above link.

Upvotes: 4

Views: 705

Answers (3)

Gareth
Gareth

Reputation: 199

hil,

The union method creates a range with n areas.

It is interesting that this Application.Union(Range("a1:c2"), Range("a1:c2"), Range("a1:c2")) creates a single area. And in fact for the case where one of the ranges contains all the others entirely you will only get back the 1 area. eg Application.Union(Range("a1:f2"), Range("b2:d2")). Another interesting example is Application.Union(Range("a1:f2"), Range("b2:d3"), Range("d2:e3")) this merged range(b2:e3) and created 2 areas.

A point to make at this point is that contiguous ranges have to be rectangular.

So Union will create the most efficient contiguous blocks of ranges, but there may still be some overlap.

CPearson's website has been around since the dawn of time, so it is very unlikely that you will find a better source for many problems. (I am not CPearson).

Regards Gareth

Upvotes: 1

John Coleman
John Coleman

Reputation: 51998

I find it somewhat pointless to speculate much about 20-year old closed-source design decisions, although it is interesting to note that the way that Excel-VBA handles Union meshes with the way Excel treats multiple range selections in spreadsheet functions. In this:

enter image description here

the sum evaluates to 12 and not 10. Thus it seems likely that VBA's approach was an attempt to come up with a Range object that corresponds to how Excel itself treats overlapping selections. This would push back the design-decision to pre-VBA Excel.

If Union doesn't do what you want, then you need to write a different function. Chip Pearson is an extremely good VBA programmer, so I would be surprised if there is any easy work-around that he missed. Your actual question discussed the count of the union. If that is what you want, you could write a smaller function:

Function UnionCount(A As Range, B As Range) As Long
    Dim adjustment As Long
    If Not Intersect(A, B) Is Nothing Then
        adjustment = Intersect(A, B).Cells.Count
    End If
    UnionCount = Union(A, B).Cells.Count - adjustment
End Function

Then UnionCount(Range("A1:B2"), Range("B1:B4")) evaluates to 6.

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9461

Yes, the Union operator is really more like a UNION ALL statement in SQL. Excel's Union operator does not return the distinct set of cells.

Upvotes: 0

Related Questions