I want to use the Concatenate function below, but I need to use it multiple
times on a large spreadsheet. How can I get Excel to do this automatialcly
based on a particular word in Column a?

Range("A9").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[1],R[1]C[1],R[1]C[2],R[1]C[3],R[1]C[4])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B9:J9").Select
Application.CutCopyMode = False
Selection.ClearContents