Eu me deparei com esse problema em todas as versões do Excel e ainda não vi uma explicação sobre o motivo de isso acontecer.
Independentemente disso, existe um procedimento para corrigir esta aberração. O exemplo a seguir é de uma situação em que copio um intervalo de números de peça (mais de 30.000 registros) de um relatório em uma planilha separada e, em seguida, removo as duplicatas (recolhe depois que os dados foram copiados para uma nova guia):
1) Sempre classifique seus dados (do menor para o maior) primeiro. Se você tiver mais de um campo, classifique pelo campo do qual você removerá as duplicatas:
'Determine Last Row of Data To Set Range
Cells(1,1).Select
Selection.End(xlDown).Select
lastRow = ActiveCell.Row
'Define And Select Ranges For Duplicate Removal
Set mNumRange = Range(Cells(1, 1), Cells(lastRow, 1))
'Sort Range For Futile Attempt At Duplicate Removal
mNumRange.Select
ActiveWorkbook.Worksheets("TAB_3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TAB_3").Sort.SortFields.Add Key:=mNumRange, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("TAB_3").Sort
.SetRange mNumRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
2) Execute "Data - > Remove Duplicates" no campo selecionado. Isso removerá a grande maioria das duplicatas:
'Partially Remove Duplicates <sigh>
mNumRange.Select
With Selection
.RemoveDuplicates Columns:=1, Header:=xlYes
End With
Cells(1, 1).Select
3) Redefina o novo intervalo (os dados restantes ainda serão classificados de menor para o maior)
'Redefine Range After Partial Duplicate Removal
Selection.End(xlDown).Select
lastRow = ActiveCell.Row
Set mNumRange = Range(Cells(2, 1), Cells(lastRow, 1))
4) Agora, execute um procedimento linha a linha para remover as duplicatas restantes. Como os dados são classificados de menor para maior, quaisquer duplicados estarão nas linhas adjacentes:
'Execute Row-By-Row Duplicate Removal Procedure <sigh>
rowCounter = 2
deleteCounter = 0
Cells(1, 1).Select
Application.ScreenUpdating = False
Do
Cells(rowCounter, 1).Select
If ActiveCell.Value = "" Then
Cells(1, 1).Select
Exit Do
End If
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
Selection.EntireRow.Delete
deleteCounter = deleteCounter + 1
rowCounter = rowCounter - 1
End If
rowCounter = rowCounter + 1
Loop
Application.ScreenUpdating = True
MsgBox deleteCounter & " ADDITIONAL DUPLICATES DELETED", vbOKOnly
O MsgBox permite que você saiba quantas duplicatas adicionais foram deletadas.