Concatenate Two Or Multiple Columns In To One

[Excel Fox]

Another way

Sub PrepareJ()

    Dim lng As Long
    lng = Cells(Rows.Count, "B").End(xlUp).Row
    Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)
    
End Sub

[Rick Rothstein]

Another way

Sub PrepareJ()

    Dim lng As Long
    lng = Cells(Rows.Count, "B").End(xlUp).Row
    Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)
    
End Sub

Just pointing out... while it does not hurt anything to leave it in, the equal sign at the beginning of the argument to the Evaluate function is not needed... Evaluate will run the same with or without it.

[william516]

I also noticed that this code will calculate the whole workbook, can I make it only work on one sheet. even the automatic one would need the same setup if possible.