Formula to Combine multiple columns into single cell

[spectraflame]

Currently I am using the following formula to combine values from various columns into a single cell. The sheet is only capable of using columns A through Y but not all are required.

Range("Z1").Select
ActiveCell.FormulaR1C1 = _
"=RC[-25]&"",""&RC[-24]&"",""&RC[-23]&"",""&RC[-22]&"",,,""&RC[-19]&"",""&RC[-18]&"",""&RC[-17]&"",,,,""&RC[-13]&"",""&RC[-12]&"",""&RC[-11]&"",""&RC[-10]&"",,""&RC[-8]&"",,""&RC[-6]&"",""&RC[-5]&"",""&RC[-4]&"",,,"""
LR = ActiveSheet.UsedRange.Rows.Count
Range("Z1").AutoFill Destination:=Range("Z1:Z" & LR)

The columns on referenced in my current formula that are blank are skipped in the formula because I am not populating them at this time. However my concern is if there is a need to populate them in the future, I will have to manually change the formula to combine them correctly.

Does anyone have any suggestions on how to make this process more intelligent to include the columns if they are populated or just insert a "," if they are blank?

 

[baitmaster]

you could create a UDF (User Defined Function) in VBA:

Function myConcat(rng As Range)
Dim cl As Range
For Each cl In rng
    myConcat = myConcat & IIf(cl = "", ",", cl.value & ",")
Next cl
myConcat = Left(myConcat, Len(myConcat) - 1)
End Function

and then use this in Excel as your formula