Loop Question

[kevinh2320]

I need code that will loop through my work sheet and if there is only a single entry for an Account# then leave that record. But, if there is multiply rows with that account number delete all rows with that account number except the most recently entered row based on the TransactionDate. Example worksheet below - the highlighted rows represent the records to keep. Thx for any help.

 

[Peter_SSs]

First two suggestions to help get faster/better solutions from the forum

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You shouldn't need to loop each row to achieve this, Try this with a copy of your data.

Sub KeepLatest()
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlDescending, Header:=xlYes
    .RemoveDuplicates Columns:=1, Header:=xlYes
  End With
End Sub