Sub jj()
Dim ncol%, tablo, resu1(), resu2(), i&, n&, j%, p&
ncol = 3 'nombre de colonnes à adapter éventuellement
tablo = Feuil1.[A1].CurrentRegion.Resize(, ncol) 'matrice, plus rapide
ReDim resu1(1 To UBound(tablo), 1 To ncol)
ReDim resu2(1 To UBound(tablo), 1 To ncol)
For i = 2 To UBound(tablo)
If InStr(tablo(i, 1) & tablo(i, 2), " ") + InStr(tablo(i, 1) & tablo(i, 2), "-") Then
n = n + 1
For j = 1 To ncol: resu1(n, j) = tablo(i, j): Next j
Else
p = p + 1
For j = 1 To ncol: resu2(p, j) = tablo(i, j): Next j
End If
Next i
With Feuil2.[A2]
If n Then .Resize(n, ncol) = resu1
.Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
End With
With Feuil3.[A2]
If p Then .Resize(p, ncol) = resu2
.Offset(p).Resize(Rows.Count - p - .Row + 1, ncol).ClearContents 'RAZ en dessous
End With
End Sub