Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, cible$, i&, n&, b()
With Feuil2.[A1].CurrentRegion.Resize(, 8) 'Feuil2 est le CodeName de la feuille "ville"
If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1, 1).Name = "Nom" 'plage nommée
a = .Value 'matrice, plus rapide
End With
If Target.Address = "$A$2" Then
Target.Select
If Target <> "" Then
cible = LCase(Trim(Target)) & "*" 'recherche sur le début du nom
For i = 2 To UBound(a)
If LCase(Trim(a(i, 1))) Like cible Then
n = n + 1
ReDim Preserve b(1 To 6, 1 To n) 'tableau transposé
b(1, n) = a(i, 1)
b(2, n) = a(i, 3)
b(3, n) = a(i, 5)
b(4, n) = a(i, 6)
b(5, n) = a(i, 7)
b(6, n) = a(i, 8)
End If
Next
If n Then Target.Resize(n, 6) = Application.Transpose(b) 'restitution (Transpose suppose un maximum de 65536 lignes)
End If
Range("A" & n + 2 & ":F" & Rows.Count) = "" 'RAZ en dessous du tableau
End If
With [A1].CurrentRegion
If .Rows.Count > 1 Then [A2].Resize(.Rows.Count - 1).Name = "Nom" 'nouvelle plage nommée
End With
End Sub