But by changing it to a subroutine from a function, the ability to use it in a
formula in a worksheet cell is lost.

But you could pass the range (in a infrequently used) syntax:

=ConCatRange((A1:A10,B3:B5),", ")

Those inside ()'s and comma are necessary.



Gord Dibben wrote:

See Dave's improved version of the UDF

Note the UDF will not accept non-contiguous ranges.

For non-contiguous cells or ranges you can use this 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(s) 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) - Len(w))
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Gord
On Tue, 13 Jan 2009 12:06:08 -0800, JBeaucaire
wrote:

That is wonderfully concise. Is there any way to add an argument so the
delimiters (if any) are added in the user function?

=ConcatRange(", ",A1:A1000)

I would love to use the briefer version, but I need to retain the ability to
define the delimiter or use no delimiter at all.

Thanks.

--

Dave Peterson