PDA

View Full Version : Duplicates and Deletes



j19_2002
05-17-2010, 01:43 PM
Having a problem with code to identify duplicate entries using worksheets named "2010" & "2009". Once duplicates have been identified I am trying to have the entire row cut from sheet "2010" and than pasted into sheet "Duplicates".

I keep getting an error once I run it:
Code execution has been interrupted

When i click debug it highlights: .Rows(i).Delete
Below is the code I am using:

Also I have attached an example of how the original excel worksheet is set.
Someone please help...Thank You!!

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim shMatch As Worksheet
Dim shCopy As Worksheet

Set shMatch = Worksheets("2010")
Set shCopy = Worksheets("Duplicates")
With Worksheets("2009")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If Not IsError(Application.Match(.Cells(i, "A").Value, shMatch.Columns(1), 0)) Then

NextRow = NextRow + 1
.Rows(i).Copy shCopy.Rows(NextRow)
.Rows(i).Delete
End If
Next i

End With
End Sub

Simon Lloyd
05-17-2010, 11:12 PM
try .Rows(i).EntireRow.Delete

j19_2002
05-18-2010, 09:42 AM
Tried it, now it's giving me another error on the next line: End If

And if I click to continue, it continues to run the macro and find entries one by one but it always stops on the: End If

Simon Lloyd
05-18-2010, 04:49 PM
The code as you originally posted it works fine with no errors, can you post the problematic workbook?

j19_2002
05-19-2010, 10:05 AM
Ok I have attached the workbook but had to delete about 10k entries since it was too big, still creating the error though.

Essentially just trying to scan 2010 against 2009 to find entries already done in 2009 and cut them from 2010 and paste them in duplicates sheet.

Thank for all your help!

Simon Lloyd
05-19-2010, 02:04 PM
Works perfect for me as you have it, what is the error exactly?

Simon Lloyd
05-19-2010, 02:08 PM
The reason your workbook is so large is because you have formatted ALL of the 65536 rows available to you!