Button to delete oldest data

[KGards7]

Hi, I have an excel document that new records are constantly added to. What I want is a button that will remove the oldest data from it if it is a duplicate in column A.

So as an example:
Cell A1 contains the text "Reference 123"
Cell A3 contains the text "Reference 123"
Cell A15 contains the text "Reference 123"
Upon clicking the button I want the entire row of A1 and A3 to be deleted only leaving the new record of A15.

Is anyone able to help?
Thank you

 

[shinigamilight]

Try this

Sub KGards7()
       Dim lr, k As Long
       lr = Range("A" & Rows.Count).End(xlUp).Row
       Dim dic As Object
       Set dic = CreateObject("Scripting.Dictionary")
       
       For k = lr To 1 Step -1
                If dic.Exists(Range("A" & k).Value) = False Then
                    dic(Range("A" & k).Value) = k
                Else: Range("A" & k).EntireRow.Delete
                End If
       Next k
       
End Sub

 

[KGards7]

Try this

Sub KGards7()
       Dim lr, k As Long
       lr = Range("A" & Rows.Count).End(xlUp).Row
       Dim dic As Object
       Set dic = CreateObject("Scripting.Dictionary")
      
       For k = lr To 1 Step -1
                If dic.Exists(Range("A" & k).Value) = False Then
                    dic(Range("A" & k).Value) = k
                Else: Range("A" & k).EntireRow.Delete
                End If
       Next k
      
End Sub

Sorry its taken so long but thank you so much its exactly what I needed!

When running it, due to the extreme amount of data in the spreadsheet it can take sometimes close to an hour to completely remove the duplicates.

Just thinking off the top of my head of a way to speed this up, is there a way I could use the button to extract all duplicates into another tab in the workbook, then run the above code and then copy and paste the remaining data back into the original worksheet?

Of course if there is an easier/simpler way to make this run faster that I haven't though of then please feel free.

Again, thank you so much for all your help and support, it is greatly appreciated!