vba - 两个范围之间的差异

我可以找到很多有关“联合”和“相交” VBA方法的问题和示例，但是我找不到有关“设置差异”方法的任何东西吗？是否存在(通过结合和相交的组合除外)？

我试图找到一种简单的方法来获取所有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

最佳答案

我做了一些改进后，请尝试以下功能:

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)