Does anyone have a simple way of merging row data into one cell with a
comma or line separator?
4565 4565|5123|212|213 etc
5123
212
213
2112
2121
21212
221

Any clever ideas are welcome that would function on the fly for lots of
rows of data.

Thank you


--
walan
------------------------------------------------------------------------
walan's Profile: http://www.excelforum.com/member.php...o&userid=13528
View this thread: http://www.excelforum.com/showthread...hreadid=496071

Walan

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Alternative.............UDF

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:A15)


Gord Dibben Excel MVP


On Mon, 26 Dec 2005 16:07:44 -0600, walan
wrote:


Does anyone have a simple way of merging row data into one cell with a
comma or line separator?
4565 4565|5123|212|213 etc
5123
212
213
2112
2121
21212
221

Any clever ideas are welcome that would function on the fly for lots of
rows of data.

Thank you

Dibben,
This does the trick, you are the best MVP. Thank you.


--
walan
------------------------------------------------------------------------
walan's Profile: http://www.excelforum.com/member.php...o&userid=13528
View this thread: http://www.excelforum.com/showthread...hreadid=496071