两个范围之间的区别 [英] Difference between two ranges

问题描述

我可以找到关于联盟和相交VBA方法的许多问题和例子，但是对于设置差异方法我无法找到任何有用的东西。这是否存在（除了使用联合和相交的组合）？

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的所有范围，不包括range1的任何重叠范围2，而不知道任一范围的大小或形状。

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

推荐答案

有一点：

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屋！