Concatenate via code..

[Zack Barresse]

Hello,

I'm wondering what the easiest way would be to Concatenate an unknown amount of textual cells into a single string seperated with a specified character?

For example:


A1 B1 C1 D1
Hi There It's Me Hiya! It's you!

I want to be able to say, I want to start in A1, the text may not end in D1, but can use in a IV1.end(xltoright) to find it, and have it turn out looking like this:


Hi There,It's Me,Hiya!,It's you!

Thanks! :)

[Ken Wright]

Posted in the Excel groups by J E McGimpsey



Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 1 - (Len(sDelimiter) > 0))
'Call as =MultiCat(A1:A5)
'you can add a delimiter if you wish:
' =MultiCat(A1:A5,",")
End Function


Regards
Ken................

[mdmackillop]

Hi Zack,
for a sub try.
MD



Sub CoCat()
Dim MyRange As Range, txt As String, Cel
Set MyRange = Intersect(Rows("1:1"), ActiveSheet.UsedRange)
For Each Cel In MyRange
txt = txt & Cel & ","
Next
txt = Left(txt, Len(txt) - 1)
If ActiveCell.Row() = 1 Then MsgBox "Select a cell in another row": Exit Sub
ActiveCell.Formula = txt
End Sub

[Tommy]

On the delimiter I prefer "|". The reason is in names and address the usual seperater is a , ex Smith, John E. or China, TX 77777

Later

[Zack Barresse]

Thanks Ken, MD, Tommy! That's why I love this site!! Three of the most respected (imho) coder's I've ever seen, w/ absolutely great solutions!!

I ended up with a variation of MD's code. Altered for the personal need but not much different. Ken, I'll keep that little gem for my personal file. :)

Thank you! :vv

[mdmackillop]

Glad, to be!, of help, MD