Solved: Deleting duplicate data

[silentsound]

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


[VBA]
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
[/VBA]

[Aussiebear]

Try this

Change

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

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

[vzachin]

i usually find the last row using xlUp instead of xlDown; and theN delete starting from the bottom

[vba] 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
[/vba]

[silentsound]

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