1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| Option Base 1
'trier les doublons dans une plage de cellules en prenant en compte plusieurs colonnes
Sub test()
tri_doublon_multicolonne Range("a1:c" & Range("a" & Rows.Count).End(xlUp).Row)
End Sub
Public Function tri_doublon_multicolonne(plage As Range)
Dim dico, tablo, tablosd(), e, textrec As String
Set dico = CreateObject("Scripting.Dictionary")
tablo = plage.Value
plage.Clear
For i = 1 To UBound(tablo) 'boucle sur toute les ligne du tableau(tablo)
textrec = ""
For a = 1 To plage.Columns.Count - 1
textrec = textrec & tablo(i, a)
Next
If Not dico.Exists(textrec) Then dico(textrec) = i
Next
k = dico.keys
i = dico.items
For n = 0 To dico.Count - 1
'MsgBox k(n) & ", " & i(n)
e = e + 1
ReDim Preserve tablosd(1 To dico.Count, 3)
tablosd(e, 1) = tablo(i(n), 1)
tablosd(e, 2) = tablo(i(n), 2)
tablosd(e, 3) = tablo(i(n), 3)
Next
Cells(plage.Row, plage.Column).Resize(UBound(tablosd), 3) = tablosd
End Function |
Partager