CODEWITHSUNDEEP
excelvba
Jossy
Jossy

Reputation: 989

Union not picking up all ranges

I'm having some challenges with a Union not working in a big macro. It's supposed to join together a few ranges but it only seems to pick up the first range. To help isolate the challenge I've trimmed all the code down to this:


Dim copiedrange As Range
Dim SrcWB As Workbook
Dim SrcWS As Worksheet

Set SrcWB = Workbooks("all-euro-data-2018-2019 (1)")
Set SrcWS = SrcWB.Sheets("E1")

i = 1

Set copiedrange = Union(SrcWS.Range("A" & i & ":F" & i), SrcWS.Range("AX" & i), _
SrcWS.Range("AZ" & i), SrcWS.Range("BH" & i & ":BJ" & i))

MsgBox copiedrange.Columns.Count

End Sub

For some reason the column count is coming back as 6 (A to F) when I think it should be higher to account for all the other ranges. What am I missing/getting wrong/being an idiot about???

Thanks in advance!

Upvotes: 1

Views: 130

Answers (1)

BigBen
BigBen

Reputation: 49998

Union is working absolutely fine. For example, MsgBox copiedRange.Address returns $A$1:$F$1,$AX$1,$AZ$1,$BH$1:$BJ$1.

You're encountering the behavior of Range.Columns.Columns.Count is returning the number of columns in the first area ($A$1:$F$1).

From the Range.Columns documentation:

When applied to a Range object that's a multiple-area selection, this property returns columns from only the first area of the range. For example, if the Range object has two areas — A1:B2 and C3:D4 — Selection.Columns.Count returns 2, not 4. To use this property on a range that may contain a multiple-area selection, test Areas.Count to determine whether the range contains more than one area. If it does, loop over each area in the range.

Upvotes: 3

Related Questions