Range Difference VBA

Hi All,

Here is a method to extract the range difference.

Code:

---

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

---

Quote:

---

Originally Posted by Admin

Hi All,

Here is a method to extract the range difference.

Code:

---

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...

Code:

---

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

---

Thanks Rick, code modified.