Compare two sheets and copy new and delete duplicates in excel

Question

I have two sheets named source and solution and I have to perform following activities.

1. Copy new records from source to solution.
2. Update records if rows present in source and have updated data.
3. Delete rows from solutions if rows not present in source file.

I have written following vba code.

Sub processDuplicate()
 Application.ScreenUpdating = False
 'Declare variables
 Dim Sample As String, test As String
 Dim var As Variant, iRow As Long, iRowL As Long, RowsRange As String
 Dim Row As Long, FoundDup As Range, SalesSampleOrderClmn As Variant
 test = "test"
 Sample = "Sample"
 Dim CountTempNoDup As Integer
 Dim CountTempDup As Integer
 SalesSampleOrderClmn = Application.Match("Sales", Sheets(Sample).Rows(1), 0)
 Debug.Print SalesSampleOrderClmn
 'Set up the count as the number of filled rows in the first column of Sheet1.
 iRowL = Worksheets(Sample).Cells(Rows.count, SalesSampleOrderClmn).End(xlUp).Row
 Debug.Print iRowL
 'Cycle through all the cells in that column:
 For iRow = 2 To iRowL
 'Call subRemoveNotRequiredRows(TempData, SaleSamples)
 '(CompareFrom As String, DeleteFrom As String)
 Set FoundDup = Worksheets(test).Range("D:D").Find(Cells(iRow, SalesSampleOrderClmn), LookIn:=xlValues, lookat:=xlWhole)
 If FoundDup Is Nothing Then
 CountTempNoDup = CountTempNoDup + 1
 Worksheets(Sample).Cells(Row, ColumnNumber).EntireRow.Delete
 Else:
 CountTempDup = CountTempDup + 1
 RowsRange = "A" & iRow & ":M" & iRow
 Worksheets(test).Range(RowsRange).Copy Worksheets(Sample).Range(RowsRange)
 Worksheets(test).Cells(iRow, SalesSampleOrderClmn).EntireRow.Delete
 End If
 Next iRow
 Debug.Print CountTempNoDup; "No Dup"
 Debug.Print CountTempDup; "Dup"
 'Application.ScreenUpdating = True
 Worksheets(test).Rows(1).EntireRow.Delete
 Worksheets(test).UsedRange.Copy
 'Sheets(Test).Copy After:=Worksheets(Sample).Cells(Rows.count, 1).End(xlUp)
 Worksheets(Sample).Cells(Rows.count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 Worksheets(test).Delete
End Sub

I'm facing performance issues in excel and I need help to increase performance and its correctness.

I have one problem with deleting entire row. It's not deleting entire row on till column L.

Worksheets(Sample).Cells(Row, ColumnNumber).EntireRow.Delete

• DataWriter: Are you comfortable working with ADO? I'm wondering if it would be faster to perform some of the operations using recordsets insetad of the worksheet.
• DigguDg: Thanks @DataWriter, will look ADO. But not much aware of that. can you help me to know which operation can be done using ADO?
• 200_success: "I need help to increase ... its correctness" — Is this code working correctly as intended or not (for small inputs at least)?
• DataWriter: My initial idea is something like: create a recordset with the source "Select * from [sheet1$] where yourKeyField NOT IN (SELECT yourKeyField FROM [sheet2$]". Then write these records to the spreadsheet. Then do similar operations for the other steps. It will require some familiarity with SQL and ADO,though.
• DigguDg: @200_success Yes it is working for small amount of data

Answer 1

I think you should be having performance issues because you have this:

 'Cycle through all the cells in that column:
 For iRow = 2 To iRowL
 'Call subRemoveNotRequiredR...

So you're going top to bottom and every time you go down and delete a row everything needs to shift up and recalculate and whatever. Go bottom up:

Print iRowL
 'Cycle through all the cells in that column:
 For iRow = iRowL to 2 step -1
 'Call subRemoveNotRequiredRows(TempData, SaleSamples)