One way:

Public Sub Concat(rng As Range)
Dim rCell As Range
Dim sOut As String
For Each rCell In Selection
sOut = sOut & "," & rCell.Text
Next rCell
rng.Value = Mid(sOut, 2)
End Sub


In article ,
"MAB" wrote:

I want to write a subroutine/macro that will execute on a selected range of
cells ( of a single column ). That range will be passed to the subroutine
and the sub will concatenate all the values of the cells within the range
with commas "," seperating each value. And finally the sub will place the
resulting string say in cell A1. for example If I select range A4:A6 which
contain values 4,5,6 respectively then when I run the macro/sub the
resulting string "4,5,6" should appear in A1. how can that be done. I can do
the concatenation part but dont know about working on selected ranges.

Note: The range sould be passed as argument to the sub and the range does
not span multiple columns.


thx