Concatenate via code..

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:

Code:

---

  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:

Code:

---

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

---

Thanks! :)

Posted in the Excel groups by J E McGimpsey

Code:

---

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................

Hi Zack,
for a sub try.
MD

Code:

---

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

---

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

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

Glad, to be!, of help, MD