Private Sub CommandButton1_Click()
Dim A$
Dim B$
Dim S As Worksheet
Dim lastLig&
Dim R As Range
Dim var
Dim i&
On Error GoTo Erreur
Application.ScreenUpdating = False
A$ = ComboBox1.SelText
B$ = ComboBox2.SelText
With ThisWorkbook
.Sheets(FEUILLE).Copy after:=Sheets(.Sheets.Count)
End With
Set S = ActiveSheet
If A$ = "" And B$ = "" Or _
A$ = TOUS And B$ = TOUS Then
Unload Me
Exit Sub
End If
If A$ <> TOUS And A$ <> "" Then
lastLig& = S.UsedRange.Rows.Count
Set R = S.Range("c1:c" & lastLig& & "")
var = R
For i& = lastLig& To 1 Step -1
If var(i&, 1) <> A$ Then S.Rows(i&).Delete
Next i&
End If
If B$ <> TOUS And B$ <> "" Then
lastLig& = S.UsedRange.Rows.Count
Set R = S.Range("d1:d" & lastLig& & "")
var = R
For i& = lastLig& To 1 Step -1
If var(i&, 1) <> B$ Then S.Rows(i&).Delete
Next i&
End If
S.[a1].Select
ActiveWindow.ScrollRow = 1
Erreur:
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim var
Dim i&
Dim j&
Dim T()
With ThisWorkbook.Sheets(FEUILLE)
.Activate
'--- ComboBox1 colonne C ---
var = .Range("c1:c" & .[c65536].End(xlUp).Row)
Call algoTri(LBound(var, 1), UBound(var, 1), var)
j& = 2
ReDim T(1 To j&)
T(1) = TOUS
T(2) = var(1, 1)
For i& = 2 To UBound(var, 1) - 1
If var(i& + 1, 1) <> var(i&, 1) Then
j& = j& + 1
ReDim Preserve T(1 To j&)
T(j&) = var(i& + 1, 1)
End If
Next i&
With ComboBox1
.List() = T
.Text = T(1)
If UBound(T) > 40 Then
.ListRows = 40
Else
.ListRows = UBound(T)
End If
End With
'--- ComboBox2 colonne D ---
Erase T
var = .Range("d1:d" & .[c65536].End(xlUp).Row)
Call algoTri(LBound(var, 1), UBound(var, 1), var)
j& = 2
ReDim T(1 To j&)
T(1) = TOUS
T(2) = var(1, 1)
For i& = 2 To UBound(var, 1) - 1
If var(i& + 1, 1) <> var(i&, 1) Then
j& = j& + 1
ReDim Preserve T(1 To j&)
T(j&) = var(i& + 1, 1)
End If
Next i&
With ComboBox2
.List() = T
.Text = T(1)
If UBound(T) > 40 Then
.ListRows = 40
Else
.ListRows = UBound(T)
End If
End With
End With
End Sub