Non-Intersect Range VBA
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 Different = Nothing
Else
Set rngUnion = Union(Range1, Range2)
Set rngIntersect = Intersect(Range1, Range2)
If rngIntersect Is Nothing Then
Set Difference = rngUnion
Else
varFormulas = rngUnion.Formula
rngUnion.Value = 0
rngIntersect.ClearContents
Set Difference = rngUnion.SpecialCells(xlCellTypeConstants)
rngUnion.Formula = varFormulas
End If
End If
End Function