subroutine to concatenate strings

[MAB]

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

 

[Norman Jones]

Hi MAB,

Try:

Sub Concat(RngConcat As Range, CellDest As Range)
Dim cel As Range
Dim sStr As String

For Each cel In RngConcat
If Len(sStr) > 1 Then
sStr = sStr & "," & cel.Value
Else
sStr = cel.Value
End If
Next cel
CellDest = sStr

End Sub

As an example of usage:

Sub Demo()
'Add new sheet to avoid problems with existing data!
Sheets.Add 'To avoid problems with existing data!
'Write demo data
Range("A4") = "Rum"
Range("A5") = "Brandy"
Range("A6") = "Vodka"

'And here we concatenate data to A1
Concat Range("A4:A6"), Range("A1")

End Sub

 

[JE McGimpsey]

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