Sub Test()
'Efgé pour sandrabordeaux
'http://www.excel-downloads.com/forum/206244-concatenation-infinie.html#post1290012
Dim i&, Treport As Variant, TCommunes As Variant, TAct As Variant
With Sheets("Feuil1")
TCommunes = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(3))
TAct = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(3))
ReDim Treport(1 To UBound(TCommunes, 1) * UBound(TAct, 1), 1 To 1)
For i = LBound(TCommunes, 1) To UBound(TCommunes, 1)
For J = LBound(TAct, 1) To UBound(TAct, 1)
k = k + 1
Treport(k, 1) = TCommunes(i, 1) & " " & TAct(J, 1)
Next J
Next i
If UBound(Treport, 1) > .Rows.Count Then MsgBox "Le nombre de lignes dépasse le nombre de ligne de la feuille" & vbLf & _
"Seuls les " & Format(Rows.Count - 1, "#,##0") & " seront prises en compte" & vbLf _
, 64, "Dépassement du nombre de lignes"
.Cells(2, 5).Resize(IIf(UBound(Treport, 1) > .Rows.Count - 1, Rows.Count - 1, UBound(Treport, 1))) = Treport
End With
End Sub