excel - 联盟没有选择所有范围

Question

我遇到了一些挑战，工会不在一个大的宏观范围内工作。它应该将几个范围连接在一起，但它似乎只拾取第一个范围。为了帮助隔离挑战，我将所有代码缩减为:

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

出于某种原因，当我认为考虑到所有其他范围时，列数又回到了 6(A 到 F)。我错过了什么/弄错了/是个白痴？？？

提前致谢!

Answer 1

Union工作得很好。例如，MsgBox copiedRange.Address返回 $A$1:$F$1,$AX$1,$AZ$1,$BH$1:$BJ$1 .

您遇到了 Range.Columns 的行为. Columns.Count返回第一个 中的列数面积 ($A$1:$F$1)。

来自 Range.Columns 文档:

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.