Private Sub Worksheet_Activate()
'Feuil1 et Feuil2 sont les CodeNames des feuilles, à adapter
Dim tablo, d As Object, i&
tablo = Feuil1.[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tablo)
d(tablo(i, 1)) = tablo(i, 2) Like "*non*" Or d(tablo(i, 1))
Next
With Feuil2.[A1].CurrentRegion.Resize(, 3)
tablo = .Value 'matrice, plus rapide
For i = 2 To UBound(tablo)
If d(tablo(i, 1)) Then tablo(i, 2) = "": tablo(i, 3) = ""
Next
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
.Value = tablo
Application.EnableEvents = True 'réactive les évènements
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Activate 'lance la macro
End Sub