Range Difference VBA

[Admin]

Hi All,

Here is a method to extract the range difference.


Option Explicit

Function RangeDifference(ByRef ActualRange As Range, MinusRange As Range) As Range

'// Author : Krishnakumar
'// Created on : 08-Feb-2012

Dim rngTemp As Range
Dim wksOriginal As Worksheet
Dim wksTemp As Worksheet
Dim lngSU As Long

With Application
lngSU = .ScreenUpdating
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

Set wksOriginal = ActualRange.Parent
Set wksTemp = Worksheets.Add

Set rngTemp = wksTemp.Range(ActualRange.Address)
rngTemp.Value = "z"

wksTemp.Range(MinusRange.Address).Clear
On Error Resume Next
Set RangeDifference = wksOriginal.Range(rngTemp.SpecialCells(xlConstants ).Address)

wksTemp.Delete

With Application
.ScreenUpdating = lngSU
.EnableEvents = True
.DisplayAlerts = True
End With

End Function

[Rick Rothstein]

Hi All,

Here is a method to extract the range difference.


Option Explicit

Function RangeDifference(ByRef ActualRange As Range, MinusRange As Range) As Range

'// Author : Krishnakumar
'// Created on : 08-Feb-2012

Dim rngTemp As Range
Dim wksOriginal As Worksheet
Dim wksTemp As Worksheet
Dim lngSU As Long

With Application
lngSU = .ScreenUpdating
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

Set wksOriginal = ActualRange.Parent
Set wksTemp = Worksheets.Add

Set rngTemp = wksTemp.Range(ActualRange.Address)
rngTemp.Value = "z"

wksTemp.Range(MinusRange.Address) = 1
On Error Resume Next
Set RangeDifference = wksOriginal.Range(rngTemp.SpecialCells(2, 2).Address)

wksTemp.Delete

With Application
.ScreenUpdating = lngSU
.EnableEvents = True
.DisplayAlerts = True
End With

End Function
While not critical in any sense of the word, I think it would be more straightforward to simply Clear the MinusRange rather than fill it with 1's, then just use a single argument (xlConstants) for the SpecialCells function call instead of the two arguments required by your method. So, my suggestion would be to replace the lines of code highlighted in red with these...


wksTemp.Range(MinusRange.Address).Clear
On Error Resume Next
Set RangeDifference = wksOriginal.Range(rngTemp.SpecialCells(xlConstants ).Address)

[Admin]

Thanks Rick, code modified.