Private Sub CommandButton1_Click()
Dim tablo(), i&, j&, n&, xrg As Range, elem
Application.ScreenUpdating = False
With Sheets("Feuil1")
' pour chaque plage
For i = 1 To 4
Set xrg = .Range("Plage" & i) ' Plagei
' créer un tableau vide de nombre de lignes de Plagei et à 2 colonnes
ReDim tablo(1 To xrg.Count / 2, 1 To 2): n = 0
' pour chaque élément de la 2ième colonne de Plagei
For Each elem In xrg.Columns(2).Cells
If elem <> Val(ComboBox1) Then
'si le numero est <> de combobox, on met l'laligne dans le tablo
n = n + 1: tablo(n, 1) = elem.Offset(, -1): tablo(n, 2) = elem
End If
Next elem
xrg = tablo ' on affecte à Plagei le tablo
Next i
End With
UserForm_Initialize
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
Dim C As Range, dico, i As Integer, tablo, ech
Set dico = CreateObject("scripting.dictionary")
With Sheets("Feuil1")
' ^poter les doublons
For Each C In .Range("Maplage")
If Not C = "" Then dico(C.Value) = Empty
Next C
tablo = dico.keys
' trier les valeurs de la combobox
Do
ech = ""
For i = LBound(tablo) To UBound(tablo) - 1
If tablo(i) > tablo(i + 1) Then ech = tablo(i): tablo(i) = tablo(i + 1): tablo(i + 1) = ech
Next i
Loop Until ech = ""
' affecter le tableau trié à la ComboBox
ComboBox1.List = tablo: ComboBox1.ListIndex = -1
End With
End Sub