Solved: Keep only the newest data?!?

[james123]

Quick question I need some help with please guys.

I have a huge spreadsheet 7000 rows. But there are a lot of repeats. All I want to do is only keep the newest data. For example:

Device ?FE053422? shipped on the 21/08/2006 and 30/08/2006 and finally the 21/02/2007

The macro needs to delete both rows with the information about the device shipping on the 21/8/2006 and 30/08/2006 and only keep the newest row the 21/2/2007

How would I go about doing this?


Just add another spanner into works some times the shipping data is missing, in this case the row needs to be kept even if newer information exists.

Attached is a small example with the correct column names and data types.

[james123]

Managed to crack this one my self in the end.

Thanks James

[VBA]Sub KeepNewDates()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "F").Value Then
If Cells(RowNdx, "F").Value <= Cells(RowNdx - 1, "F").Value Then
Rows(RowNdx).Delete
Else
Rows(RowNdx - 1).Delete
End If
End If
Next RowNdx
End Sub
[/VBA]