Difference between two cell ranges

[georgemathew46]

Hi,
i have two variables which holds a range
x=a1:f16
y=a16:f16
i want to do a difference. but its not working.

i want my output to be

z=x-y i.e z=a1:f15

Please help me out...

 

[Asala42]

This function worked for me, from:
excel vba - VBA Difference between two ranges - Stack Overflow

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

 

[georgemathew46]

Great thanks for instant reply bro..

But i am having some issues again.. am not able to print the output range or view it..

Could you please help ?

Dim rng As Range
Dim SrcRange As Range ' you should always declare things explicitly
Set SrcRange = Sheets("sheet1").Range("A1:c3")
Dim trgRange As Range ' you should always declare things explicitly
Set trgRange = Sheets("sheet1").Range("A3:c3")
Set rng = SetDifference(SrcRange, trgRange)
Sheets("Sheet1").Range(rng).Select

----------------------------------


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

 

[georgemathew46]

Update Bro..
Cleared the issue by self..
Many Thanks Bro...


Sub Macro1()
'
' Macro1 Macro
'
'
Dim rng As Range
Dim SrcRange As Range ' you should always declare things explicitly
Set SrcRange = Sheets("sheet1").Range("A1:f16")
Dim trgRange As Range ' you should always declare things explicitly
Set trgRange = Sheets("sheet1").Range("A14:f16")
Set rng = SetDifference(SrcRange, trgRange)
MsgBox rng.Address
End Sub
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

 

[georgemathew46]

Thanks for your reach out in solving my problem "difference between two cell range address"

Could you also provide the same for addition ?

like x=a1:f16
y=a17:f17
i want my output to be a1:f17 ..

Could you please help out ?

 

[Asala42]

I think for addition you can just use a Union statement. For example:

Function AddRange(Rng1 As Range, Rng2 As Range) As Range
    Set AddRange = Union(Rng1, Rng2)
End Function