How do you delete duplicate values assigned to a specific name in VBA/Excel?

Question

For example I have the Table below

Name	Product
Tom	Ice Cream
Tom	Ice Cream
Tom	Toys
Jack	Ice Cream

question from:https://stackoverflow.com/questions/65933514/how-do-you-delete-duplicate-values-assigned-to-a-specific-name-in-vba-excel

Answer 1

You can fix your code like this:

Sub Delete_Duplicates()
ActiveSheet.Range("$A$1", Range("B2").End(xlDown)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

As suggested by @SiddharthRout, the right way would be:

Sub Delete_Duplicates()
Dim LR As Long
'we get position of last non empty cell in column B
LR = Range("B" & Rows.Count).End(xlUp).Row

'remove duplicates
ActiveSheet.Range("$A$1:$B$" & LR).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

Try using second one, it's indeed better, because Range("B2").End(xlDown)) does not always return the last non empty cell in column B.