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