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.