Private Sub CommandButton1_Click()
Dim t, nlig&, d As Object, i&, rest(), d1 As Object, n&, j%, a
Application.ScreenUpdating = False
With Workbooks.Open(ThisWorkbook.Path & "\Paie-Mens.xlsx").Sheets("Feuil1")
t = .Range("F5", .Range("F" & .Rows.Count).End(xlUp)(4))
nlig = UBound(t)
.Parent.Close False
End With
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t)
If t(i, 1) <> "" Then d(t(i, 1)) = ""
Next i
If d.Count = 0 Then GoTo 1 'si aucun nom
t = Range("A3:AD" & Range("F" & Rows.Count).End(xlUp)(2).Row)
ReDim rest(1 To nlig, 1 To 30) 'colonnes A à AD
Set d1 = CreateObject("Scripting.Dictionary")
'---noms en commun---
For i = 1 To UBound(t)
If t(i, 6) <> "" And d.Exists(t(i, 6)) Then
n = n + 1
d1(t(i, 6)) = ""
For j = 1 To 30
rest(n, j) = t(i, j)
Next j
End If
Next i
'---noms nouveaux---
a = d.Keys
For i = 0 To UBound(a)
If Not d1.Exists(a(i)) Then
n = n + 1
rest(n, 6) = a(i)
End If
Next i
'---restitution---
1 [AE:AF].Delete 'ces colonnes ne servent à rien...
Range("A3:AD" & Rows.Count).ClearContents 'RAZ
If n = 0 Then Exit Sub
[A3].Resize(n, 30) = rest
[A3].Resize(n, 30).Sort [F3], xlAscending, Header:=xlNo 'tri sur les noms
End Sub