[SOLVED] Non-Intersect Range VBA

Non-Intersect Range VBA

In the below code rngIntersect.Address returns A10. Is there way where in i can get all ranges excluding intersection without looping?

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

Solution

I had posted this question to msdn forum with lack of response from SO and got the required solution. I have tested the code and it works fine. I hope it helps.

Here is the link for post on 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