非交叉范围VBA

Question

在下面的代码中，rngIntersect.Address返回A10。有没有一种方法可以在不循环的情况下获取排除交集之外的所有范围？

Sub NotIntersect()

    Dim rng As Range, rngVal As Range, rngIntersect As Range
    Set rng = Range("A1:A10")
    Set rngVal = Range("A10")

    Set rngIntersect = Intersect(rng, rngVal)
    MsgBox rngIntersect.Address

End Sub

- Santosh

---

你是想要 a1:a9 还是除了 a10 以外的所有内容？ - glh

Answer 1

我曾在 MSDN 论坛上发布了这个问题，但 SO 没有回应。最终我在 MSDN 上找到了需要的解决方案。我已经测试过代码，它可以正常工作。希望对你有所帮助。

这是在 MSDN 上发布的帖子的链接。

Sub NotIntersect()
        Dim rng As Range, rngVal As Range, rngDiff As Range
        Set rng = Range("A1:A10")
        Set rngVal = Range("A5")
        Set rngDiff = Difference(rng, rngVal)
        MsgBox rngDiff.Address
    End Sub
    
    Function Difference(Range1 As Range, Range2 As Range) As Range
        Dim rngUnion As Range
        Dim rngIntersect As Range
        Dim varFormulas As Variant
        If Range1 Is Nothing Then
            Set Difference = Range2
        ElseIf Range2 Is Nothing Then
            Set Difference = Range1
        ElseIf Range1 Is Nothing And Range2 Is Nothing Then
            Set Difference = Nothing
        Else
            Set rngUnion = Union(Range1, Range2)
            Set rngIntersect = Intersect(Range1, Range2)
            If rngIntersect Is Nothing Then
                Set Difference = rngUnion 'Updated "Different" to "Difference"
            Else
                varFormulas = rngUnion.Formula
                rngUnion.Value = 0
                rngIntersect.ClearContents
                Set Difference = rngUnion.SpecialCells(xlCellTypeConstants)
                rngUnion.Formula = varFormulas
            End If
        End If
    End Function

- Santosh

Answer 2

你要找的是集合论术语中的“补集”。请参阅Wikipedia。这可以在不遍历两个范围内的每个单元格的情况下完成（对于具有许多单元格的范围来说，这将是一个巨大的开销），但是您需要循环遍历范围内的每个区域。该循环快速高效。以下是代码：

Public Function NotIntersect(Range1 As Range, Range2 As Range) As Range
Dim NewRange As Range, CurrentArea As Range, CurrentNewArea(1 To 4) As Range, r As Range
Dim c%, a%
Dim TopLeftCell(1 To 2) As Range, BottomRightCell(1 To 2) As Range
Dim NewRanges() As Range, ColNewRanges() As New Collection
Const N% = 2
Const U% = 1

If Range1 Is Nothing And Range2 Is Nothing Then
    Set NotIntersect = Nothing
ElseIf Range1.Address = Range2.Address Then
    Set NotIntersect = Nothing
ElseIf Range1 Is Nothing Then
    Set NotIntersect = Range2
ElseIf Range1 Is Nothing Then
    Set NotIntersect = Range1
Else

    Set TopLeftCell(U) = Range1.Cells(1, 1)
    Set BottomRightCell(U) = Range1.Cells(Range1.Rows.Count, Range1.Columns.Count)

    c = Range2.Areas.Count
    ReDim ColNewRanges(1 To c)
    ReDim NewRanges(1 To c)

    For a = 1 To c
        Set CurrentArea = Range2.Areas(a)
        Set TopLeftCell(N) = CurrentArea.Cells(1, 1)
        Set BottomRightCell(N) = CurrentArea.Cells(CurrentArea.Rows.Count, CurrentArea.Columns.Count)

        On Error Resume Next
        Set ColNewRanges(a) = New Collection
        ColNewRanges(a).Add Range(TopLeftCell(U), Cells(TopLeftCell(N).Row - 1, BottomRightCell(U).Column))
        ColNewRanges(a).Add Range(Cells(TopLeftCell(N).Row, TopLeftCell(U).Column), Cells(BottomRightCell(N).Row, TopLeftCell(N).Column - 1))
        ColNewRanges(a).Add Range(Cells(TopLeftCell(N).Row, BottomRightCell(N).Column + 1), Cells(BottomRightCell(N).Row, BottomRightCell(U).Column))
        ColNewRanges(a).Add Range(Cells(BottomRightCell(N).Row + 1, TopLeftCell(U).Column), BottomRightCell(U))
        On Error GoTo 0

        For Each r In ColNewRanges(a)
            If NewRanges(a) Is Nothing Then
                Set NewRanges(a) = r
            Else
                Set NewRanges(a) = Union(NewRanges(a), r)
            End If
        Next r

    Next a

    For a = 1 To c
        If NewRange Is Nothing Then
            Set NewRange = NewRanges(a)
        Else
            Set NewRange = Intersect(NewRange, NewRanges(a))
        End If
    Next a

    Set NotIntersect = Intersect(Range1, NewRange) 'intersect required in case it's on the bottom or right line, so a part of range will go beyond the line...

End If    
End Function

测试如下：

Sub Test1()
    NotIntersect(Range("$A$1:$N$24"), Range("$G$3:$H$12,$C$4:$D$7,$A$13:$A$15")).Select
End Sub

- as9876

Answer 3

据我所知，这方面没有“清理”函数。如果要求“不循环”很重要，您可以尝试以下方法（这是一种“方法”，而不是工作代码）：

- create a new sheet
- find intersection of ranges
- set range from top left to bottom right of intersection to 0
- set range1 to 1
- set all values in range2 = XOR of values that are there (so 1 becomes 0, and 0 becomes 1)
- find all cells with a 1 - their address is the "non-intersection"
- delete the temp sheet

我相信每个都可以在不使用循环的情况下完成 - 但这是一种可怕的hack...

- Floris