PDA

View Full Version : Solved: Keep only the newest data?!?



james123
02-21-2007, 08:28 PM
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
02-21-2007, 09:54 PM
Managed to crack this one my self in the end.

Thanks James

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