当我需要将多个range
对象组合在一起时,我使用这个函数来替代Application.Union
,其中“零个或多个”范围可能为Nothing
:
Function union(ParamArray rgs() As Variant) As Range
Dim i As Long
For i = 0 To UBound(rgs())
If Not rgs(i) Is Nothing Then
If union Is Nothing Then Set union = rgs(i) Else Set union = Application.union(union, rgs(i))
End If
Next i
End Function
使用示例:
Sub demo_union()
Dim rg1 As Range, rg2 As Range, rg3 As Range, newRg As Range
Set rg1 = Range("A1")
Set rg3 = Range("C3")
Set newRg = union(rg1, rg2, rg3)
newRg.Select
End Sub
以下是一种不会在返回的范围中重复重叠单元格的变体。
通常情况下,当使用Application.Union
(或上述函数)组合重叠范围(例如,A1:B2
和B2:C3
)时,结果将具有多个重叠单元格的副本。
例如,使用以下代码:
![overlapping ranges A1:B2 and B2:C3](https://istack.dev59.com/AJVgm.webp)
Application.Union([A1:B2], [B2:C3]).Cells.Count '8 cells (repeats B2)
↑...返回8个单元格:A1
B1
A2
B2
B2
C2
B3
C3
(并且For Each
循环将有8次迭代。)
以下的union2
函数解决了这个问题,它仅返回唯一的单元格,并处理空范围(而不会产生令人恼火的模糊错误信息"Invalid Procedure call or argument"
)。
Debug.Print union2([A1:B2], [B2:C3]).Cells.Count '7 cells
↑ ...返回7个单元格:A1
B1
A2
B2
C2
B3
C3
(For Each
循环将进行7次迭代。)