PDA

View Full Version : Solved: Deleting duplicate data



silentsound
07-29-2010, 02:19 PM
Hi all

I have written the following code for a spreadsheet which compares data on two sheets. Unfortunately I can't attach the spreadsheet. However I have pasted the code below. The format of the data is letters in column C in numbers in column D (starting in row 2). The aim is to remove all data on the "big list" that is also on the "small list" (when both the letters and numbers match). Just that.

The reason believe it doesn't work is that it removes data when executed a second or third time, which obviously shouldn't be the case.

Would someone be able to explain why the code below doesn't work? Perhaps I am missing something obvious...
anyhow all help much appreciated :)


Oh an also would a kind person mind suggesting something like a break statement to make this more efficient. I don't know what the equivalent is in VBA or at least how to use it.

cheers
silentsound



Option Explicit
Sub compare_date()
Application.ScreenUpdating = False
Dim smallListsheet, bigListsheet As Worksheet
Dim sLLast, bLLast As Integer
Set smallListsheet = Worksheets("Small list")
Set bigListsheet = Worksheets("Big list")

sLLast = smallListsheet.Cells(2, 3).End(xlDown).Row
bLLast = bigListsheet.Cells(2, 3).End(xlDown).Row

MsgBox (sLLast)
MsgBox (bLLast)

Dim i, j As Integer

For i = 2 To bLLast
For j = 2 To sLLast
If smallListsheet.Cells(j, 3).Text = bigListsheet.Cells(i, 3).Text Then bigListsheet.Cells(i, 3).EntireRow.Delete
End If
Next j

Next i

End Sub

Aussiebear
07-29-2010, 03:17 PM
Try this

Change

If smallListsheet.Cells(j, 3).Text = bigListsheet.Cells(i, 3).Text Then bigListsheet.Cells(i, 3).EntireRow.Delete

to
If smallListsheet.Cells(j, 3).Value = bigListsheet.Cells(i, 3).Value Then bigListsheet.Cells(i, 3).EntireRow.Delete

vzachin
07-30-2010, 08:20 AM
i usually find the last row using xlUp instead of xlDown; and theN delete starting from the bottom

sLLast = smallListsheet.Cells(Rows.Count, "c").End(xlUp).Row
bLLast = bigListsheet.Cells(Rows.Count, "c").End(xlUp).Row
For i = bLLast To 2 Step -1

silentsound
08-07-2010, 08:11 AM
Thanks for your suggestions guys. Just as a point of interest, the problem was in the way the data was being deleted if a match was found. The way the code is above, after a row is deleted the data in the row below would move into that row. The comparison would continue from the point where the previous data left off, hence it was not compared with all data.

The corrected code deleted just the relevant cell, preventing movement of the row below, then deleted blank cells after the comparison.

Range(whatever).SpecialCells(xlCellTypeBlanks).Select

something like that


silentsound