How do I merge the contents (separated by a comma) of 300+ cells?

G

Guest

elliott wrote:

Why do people post notes with no content? Does it take an extra 100mS
to actually type your question with some example of the data rather than
trying to put it all in the subject line which frequently gets truncated
anyhow?

Bill
 
G

Gord Dibben

Bill

Because they are using the not-so-efficient CDO and that seems to be the norm
around there.

Gord
 
G

Gord Dibben

Elliot

Are the cells contiguous or random?

If contiguous, easily done with a user defined function.

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:J30) entered in K1

Or a macro....

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


Gord Dibben Excel MVP
 
Top