title: Excel VBA - delete duplicates and keep the latest (based on date column)
body: <p>Can somebody help me? I don't even know how to start...
  I want to make a macro that deletes duplicates (based on column A) and keep the row that has the latest Date (column P). And if all duplicates have no date in column P, just keep one and delete the other duplicates.</p>
  
  <p><a href="https://i.stack.imgur.com/rcRfm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rcRfm.png" alt="enter image description here"></a></p>
  
  <p><strong>The Data in the sheet starts with row 5</strong> (not row 4 like in the picture, sorry for that). In the past I know that I had problems with deleting duplicates via macro when the table don't start with row 1 or 2.</p>
  
  <p>The table normally has around ~15 columns and ~10.000 rows.</p>
  
  <p>Some of the rows have date in column P and some row don't. So the macro should look if there are any duplicates (column A) and if so, check if there is a date in column P. If there are more duplicates with dates, the macro should delete all duplicates but keep the most recent.</p>
  
  <p>The Code I used/edited so far:</p>
  
  <pre><code>Sub DelDubs_Date()
  
  Dim Rng As Range
  Dim LastRow As Long
  Dim i As Long
  
  Application.ScreenUpdating = False
  
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  
  Set Rng = Range("A5:P" &amp; LastRow)
  
  With Rng
      .Sort key1:=Range("A5"), order1:=xlAscending, key2:=Range("P5"), order2:=xlDescending, _
          Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
  End With
  
  For i = LastRow To 2 Step -1
      If WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(i, "A")), Cells(i, "A")) &gt; 1 Then
          Rows(i).Delete
      End If
  Next i
  
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>Problem: it keeps the first row, not the one with the latest date...</p>
  
  <p><strong>TL;DR: Check for Duplicates in A, then check for dates in P, then delete all duplicates but keep the latest. And if there is no date, delete all duplicates and keep one.</strong></p>