Results 1 to 9 of 9

Code to compare data in 2 sheets and delete duplicates

Threaded View

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Code to compare data in 2 sheets and delete duplicates

    Hi everyone,
    As you can read from my title, I am trying to setup a macro that will compare the information on 2 sheets, delete the duplicates and finally paste the remaining values on another sheet (On the attached WB it needs to be pasted on the 'Difference' sheet)
    I usually run reports @ 2, 4 and 6 pm. The 4 pm contains everything that was on the 2 pm report + any updates that happened between 2 & 4 pm. Same applies for the 6 pm.

    I found a code online that allows me to find duplicates from the 2 and 4 pm reports and delete it from the 4 pm sheet. Here it is:

    Sub CleanDupes()
        Dim targetArray, searchArray
        Dim targetRange As Range
        Dim x As Long
    
        'Update these 4 lines if your target and search ranges change
        Dim TargetSheetName As String: TargetSheetName = "4 pm"
        Dim TargetSheetColumn As String: TargetSheetColumn = "A"
        Dim SearchSheetName As String: SearchSheetName = "2 pm"
        Dim SearchSheetColumn As String: SearchSheetColumn = "A"
    
        'Load target array
        With Sheets(TargetSheetName)
            Set targetRange = .Range(.Range(TargetSheetColumn & "1"), _
                    .Range(TargetSheetColumn & Rows.Count).End(xlUp))
            targetArray = targetRange
        End With
        'Load Search Array
        With Sheets(SearchSheetName)
            searchArray = .Range(.Range(SearchSheetColumn & "1"), _
                    .Range(SearchSheetColumn & Rows.Count).End(xlUp))
        End With
    
    
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        'Populate dictionary from search array
        If IsArray(searchArray) Then
            For x = 1 To UBound(searchArray)
                If Not dict.Exists(searchArray(x, 1)) Then
                    dict.Add searchArray(x, 1), 1
                End If
            Next
        Else
            If Not dict.Exists(searchArray) Then
                dict.Add searchArray, 1
            End If
        End If
    
        'Delete rows with values found in dictionary
        If IsArray(targetArray) Then
            'Step backwards to avoid deleting the wrong rows.
            For x = UBound(targetArray) To 1 Step -1
                If dict.Exists(targetArray(x, 1)) Then
                    targetRange.Cells(x).EntireRow.Delete
                End If
            Next
        Else
            If dict.Exists(targetArray) Then
                targetRange.EntireRow.Delete
            End If
        End If
    End Sub
    However, instead of deleting the values from the actual report, I would like to run the macro from the 'Difference' sheet and the former pastes the data on the sheet.
    I would also like to prevent the first row from being deleted.

    Is that possible? If it is, any help would be much appreciated.

    Thanks,
    Amar.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1