Soluution macro or function

  • Thread starter Thread starter Sue
  • Start date Start date
S

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