Hi all,
I have a button that runs the below code:
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
Basically it searches column A locates all duplicates and only keeps the most recent one and deletes the whole row of any of the now outdated duplicates.
My issue is this runs on an Excel document where there are vast quantities of data so to complete it can take on times close to an hour.
Is there a way I can click this button to cut and paste out the duplicates into a new tab, then run the above code on the new tab and then paste the results back into the main tab?
Alternatively if you have another solution so the above code can run faster then I'm open to any and all suggestions.
Thank you, I appreciate your time and help!
I have a button that runs the below code:
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
Basically it searches column A locates all duplicates and only keeps the most recent one and deletes the whole row of any of the now outdated duplicates.
My issue is this runs on an Excel document where there are vast quantities of data so to complete it can take on times close to an hour.
Is there a way I can click this button to cut and paste out the duplicates into a new tab, then run the above code on the new tab and then paste the results back into the main tab?
Alternatively if you have another solution so the above code can run faster then I'm open to any and all suggestions.
Thank you, I appreciate your time and help!