Private Sub CommandButton1_Click()
Dim c As Range, col$, a$
If CommandButton1.Caption = "FILTRER" Then
CommandButton1.Caption = "AFFICHER"
With [A1].CurrentRegion
For Each c In .Columns
If Not Intersect(c, Selection.EntireColumn) Is Nothing Then col = col & ";" & c.Column
Next
col = IIf(col = "", 0, "{" & Mid(col, 2) & "}") 'matrice sous forme de texte
a = .Rows(2).Address(0)
.Cells(2, .Columns.Count + 1) = "=SUMPRODUCT(COUNTIF(Feuil2!A:A," & a & ")*(COLUMN(" & a & ")=" & col & "))" 'critère
.AdvancedFilter xlFilterInPlace, .Cells(1, .Columns.Count + 1).Resize(2)
.Cells(2, .Columns.Count + 1) = ""
End With
Else
CommandButton1.Caption = "FILTRER"
If FilterMode Then ShowAllData
End If
End Sub