I have two sheets named source and solution and I have to perform following activities.
- Copy new records from source to solution.
- Update records if rows present in source and have updated data.
- 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