Concatenate a List?

[arcandio]

Is there an easier way to concatenate a list of cells than by the
actual concatenate function? I've got a set of cells in column 1 (A)
below cell A3 that I want all concatenated together. Can I somehow do
this without adding each specific cell to a function? Like some kind
of Array?

 

[Don Guillett]

Sub putemtogether()
For i = 4 To Cells(Rows.Count, "a").End(xlUp).Row
ms = ms & Cells(i, "a")
Next i
MsgBox ms
End Sub

 

[T. Valko]

No way to handle an array with Excel's built-in concatenation function.

One possibility is to download the *free* add-in Morefunc.xll which has a
function (MCONCAT) that will do what you want.

http://xcell05.free.fr/english/

=MCONCAT(A3:A10,",")

The last argument specifies the delimiter to use.

 

[Mark Ivey]

Here is one I put together with some help from Bob Phillips:


'---------------------------------------------------------------------------------------
' Procedure : Concat
' DateTime : 12/22/2006
' Author : Bob Phillips <[email protected]>
' Courtesy of microsoft.public.excel.programming newsgroup
' Co-Author : Mark Ivey <[email protected]>
' Purpose : To help automate the function of concatenating cell
information
'---------------------------------------------------------------------------------------
'
Sub Concat()
Dim srcrng As Range
Dim tgtrng As Range
Dim oRow As Range
Dim cell As Range
Dim tmp As String
Dim delim As String

On Error GoTo ExitSub:

Set srcrng = Application.InputBox( _
"Select input range with mouse", Type:=8)
Set tgtrng = Application.InputBox( _
"Select target column with mouse", Type:=8)
delim = Application.InputBox("Input delimiting character" & vbCr & vbCr
& _
"Examples: ~ - . * or anything else you prefer" & vbCr & vbCr & _
"If this field is left blank, a space will be used", Type:=2)
If delim = "" Then delim = " " Else
If Len(delim) > 1 Then delim = Left(delim, 1) Else
If Not srcrng Is Nothing Then
For Each oRow In srcrng.Rows
tmp = ""
For Each cell In oRow.Cells
If Not cell.Value = "" Then tmp = tmp & cell.Value & delim
Next cell
If tmp <> "" Then
Cells(oRow.Row, tgtrng.Column).Value = _
Left(tmp, Len(tmp) - 1)
End If
Next oRow
End If

ExitSub:
Exit Sub

End Sub