Formula to Combine multiple columns into single cell

spectraflame

Well-known Member
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?

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
you could create a UDF (User Defined Function) in VBA:
Code:
``````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

Replies
1
Views
2K
Replies
1
Views
389
Replies
5
Views
501
Replies
1
Views
475
Replies
18
Views
1K

1,196,340
Messages
6,014,721
Members
441,838
Latest member
ykg1991

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back