Soluution macro or function

[Sue]

I have a table below and I want to delete all material rows except the newest
date/last purchase date. Any suggestions

material purch date
A 1/2/2008
A 2/10/2007
A 2/12/2008
A 12/2/2006
A 3/23/2008
A 8/17/2007
B 1/2/2008
B 2/10/2007
B 2/12/2008
C 12/2/2006
C 3/23/2008
C 8/17/2007

 

[Pete_UK]

If you apply autofilter to the purchase date column, then when you use
the filter pull-down the dates will be in sequence with the latest
date at the bottom of the list. If you select this date, then only
those rows will be displayed. You can then easily highlight the
visible rows, click <copy> and paste only those rows to another sheet.
You can then delete the first sheet if you really don't want all the
data.

Hope this helps.

Pete

 

[Don Guillett]

Try this to sort and delete rows.
Sub lastpurdate()
lr = Cells(Rows.Count, "a").End(xlUp).Row
'sort
Range("A2:B" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, Orientation:=xlTopToBottom
'delete rows
For i = lr To 2 Step -1
If Cells(i - 1, "a") = Cells(i, "a") And _
Cells(i - 1, "b") < Cells(i, "b") Then Rows(i - 1).Delete
Next i
End Sub