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

--
Sue

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

On Feb 23, 9:42*pm, Sue wrote:
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

--
Sue

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sue" wrote in message
...
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

--
Sue