See the attached updated sample workbook.
The code is in the module mConcat. There's a very basic version and a more sophisticated version that converts the formulae to values.
Option Explicit
Sub sConcatColumns_Basic()
' Basic code
Range("CE2:CE16276").Formula = "=BJ2&""-""&BK2&""-""&BL2"
End Sub
Sub sConcatColumns_Values()
' Convert Formulae to Values
' Set column to be used for output; adjust as required
Const csColumn As String = "CE"
' Determine last row of data based on column A
Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
' Clear any existing data in the entire column
' (except header row)
Range(Cells(2, csColumn), Cells(Rows.Count, csColumn)).ClearContents
' Use the Column and Row details
With Range(Cells(2, csColumn), Cells(lLR, csColumn))
' Set format to General or formulae won't calculate
.NumberFormat = "General"
' Insert the formula
.Formula = "=BJ2&""-""&BK2&""-""&BL2"
' Set format to Text to avoid date conversion
.NumberFormat = "@"
' Convert formulae to values
.Value = .Value
' Set format back to General
.NumberFormat = "General"
End With
Application.ScreenUpdating = True
End Sub
Bookmarks