vba - 两个范围之间的差异

Question

我可以找到很多关于“Union”和“Intersect”VBA 方法的问题和示例，但我找不到关于“Set Difference”方法的任何内容？这是否存在(除了使用联合和相交的组合)？

我试图找到一种简单的方法来获取所有 range1，不包括与 range2 重叠的任何 range1，而不知道任一范围的大小或形状。

任何帮助将不胜感激。

编辑。



尝试解决方案，其中 rng1 是红色部分， rng2 是蓝色部分(已调试以检查这些是否正确):

rng = SetDifference(rng, highlightedColumns)

Function SetDifference(Rng1 As Range, Rng2 As Range) As Range
On Error Resume Next
If Application.Intersect(Rng1, Rng2).Address <> Rng2.Address Then
    Exit Function
On Error GoTo 0
Dim aCell As Range
For Each aCell In Rng1
    Dim Result As Range
    If Application.Intersect(aCell, Rng2) Is Nothing Then
        Set Result = Union(Result, aCell)
        End If
    Next aCell
Set SetDifference = Result
End If
End Function

Answer 1

在我稍微改进后试试这个功能:

Function SetDifference(Rng1 As Range, Rng2 As Range) As Range
On Error Resume Next

If Intersect(Rng1, Rng2) Is Nothing Then
    'if there is no common area then we will set both areas as result
    Set SetDifference = Union(Rng1, Rng2)
    'alternatively
    'set SetDifference = Nothing
    Exit Function
End If

On Error GoTo 0
Dim aCell As Range
For Each aCell In Rng1
    Dim Result As Range
    If Application.Intersect(aCell, Rng2) Is Nothing Then
        If Result Is Nothing Then
            Set Result = aCell
        Else
            Set Result = Union(Result, aCell)
        End If
    End If
Next aCell
Set SetDifference = Result

End Function

记得这样称呼它:

Set Rng = SetDifference(Rng, highlightedColumns)