Private Sub Worksheet_Activate() 'pour le cas où Feuil1 a été modifiée
Worksheet_Change Cells 'lance la macro
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cible As Range, x$, vide As Boolean, t, rest(), i&, n&
Set cible = [F11] 'cellule à adapter
If Intersect(Target, cible) Is Nothing Then Exit Sub
x = CStr(cible): vide = x = ""
t = Feuil1.[A1].CurrentRegion.Resize(, 4) 'matrice, plus rapide, à adapter
ReDim rest(1 To UBound(t), 1 To 3)
For i = 2 To UBound(t)
If CStr(t(i, 4)) = x Or vide Then
n = n + 1
rest(n, 1) = t(i, 1): rest(n, 2) = t(i, 2): rest(n, 3) = t(i, 3)
End If
Next
If n Then
With cible(1, 2).Resize(n, 3)
.Value = rest
.Sort cible(1, 2), xlAscending, Header:=xlNo 'tri
End With
End If
cible(1, 2).Offset(n).Resize(Rows.Count - n - cible.Row + 1, 3) = "" 'RAZ en dessous
End Sub