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
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