Sub concatenation()
'les variables:
Dim MaFormule As String, RefDeMaChaine As String, Colonne As Long, Premierligne As Long
Dim DerniereLigne As Long, Maplage As Range, ColonneResultat As String
Premierligne = 14 ' ou premierligne correspond a la premiere ligne de la plage de cellule a concatener
ColonneResultat = "AJ"
MaFormule = "=concatenate(" 'la formule commence ici
Colonne = 16 '16 correspond a la colonne P
'on creer la formule de concatenation en auto pour pas ce faire chi... ^^
For i = 1 To Colonne 'depui la colonne 1 (colonne A)
RefDeMaChaine = Columns(i).Rows(Premierligne).Address(RowAbsolute:=False)
' si PremiereLigne = 14, sans l'option RowAbsolute:=False, le resultat
' pour la premiere colonne serait $A$14
' Mais avec cette option, le resultat est $A14. ceci prepare notre formule a la fonction Autofill
MaFormule = MaFormule & RefDeMaChaine
If i <> Colonne Then
MaFormule = MaFormule & ",Char(32)" & "," 'char(32) correspond a un espace. Attention char est une fonction excel pas une fonction VBA
Else
MaFormule = MaFormule & ")" 'La formule ce termine ici
End If
Next i
'on recherche la derniere ligne de la plage de cellules a concatener sur la colonne A
DerniereLigne = Columns("A").Find("*", , , , xlByRows, xlPrevious).Row
'on determine la plage de cellules qui doit contenir le resultat de concatenation
Set Maplage = Range(ColonneResultat & Premierligne & ":" & ColonneResultat & DerniereLigne)
'on pose la formule dans la premiere cellule la colonne de resultat
Range(ColonneResultat & Premierligne).FormulaLocal = MaFormule
'on copie la formule sur toute la plage de resultat par la fonction autofil d'excel
Range(ColonneResultat & Premierligne).AutoFill Destination:=Maplage, Type:=xlFillDefault
'copy de la colonne de resultat
'Attention la suite ne marche pas si le calcul automatique d'excel est desactivé donc pensea le reactiver ici si tu l'as desactivé.
Maplage.Copy
'on ecrase la colonne de resultat par elle meme pour remplacer les formules par le resultat des formules
Maplage.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
MsgBox "Concatenation terminé ", vbInformation
End Sub