Lo he solucionado con la siguiente macro:
Sub EliminarRepetidosYRegistro()
contador = 1
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = valor Then
ActiveSheet.Next.Select
If ActiveCell.Value <> valor Then
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveCell.Value = valor
End If
ActiveSheet.Previous.Select
Selection.Delete Shift:=xlUp
contador = contador + 1
Else
If contador <> 1 Then
ActiveSheet.Next.Select
ActiveCell.Offset(0, 1).Range("a1").Select
ActiveCell.Value = contador
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveSheet.Previous.Select
End If
contador = 1
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Wend
If contador <> 1 Then
ActiveSheet.Next.Select
ActiveCell.Offset(0, 1).Range("a1").Select
ActiveCell.Value = contador
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveSheet.Previous.Select
End If
End Sub