两个范围之间的差异 [英] Difference between two ranges

问题描述

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

I can find plenty of questions and example regarding the 'Union' and 'Intersect' VBA methods but I can't find anything much regarding a 'Set Difference' method? Does this exist (other than by using combinations of union and intersect)?.

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

I'm trying to find a simple way of getting all of range1 excluding any of range1 that overlaps range2 without knowing the size or shape of either range.

任何帮助将不胜感激.

编辑.

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

Attempted solution where rng1 is the red section and rng2 is the blue section (have debugged to check these are correct):

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

推荐答案

这个功能我稍微改进一下试试:

Try this function after I have improved it a bit:

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)

这篇关于两个范围之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！