Results 1 to 3 of 3

Thread: Range Difference VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb 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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    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)

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Thanks Rick, code modified.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Correcting “Negative” Time Difference Calculation in Excel
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  2. VBA Looping Input Range and Output Range
    By Whitley in forum Excel Help
    Replies: 7
    Last Post: 04-25-2013, 09:02 PM
  3. Effective way of calculating the difference
    By zzzqinzzz in forum Excel Help
    Replies: 0
    Last Post: 12-13-2012, 03:39 PM
  4. Move or Copy Duplicate Rows to Difference Sheet
    By Vgabond in forum Excel Help
    Replies: 3
    Last Post: 12-08-2012, 12:33 PM
  5. Get time difference in excel using vba
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 10-09-2012, 07:57 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •