Concatenate values from different cells in one cell, separated by coma

[ThrottleWorks]

Hi,

I am trying to concatenate values from different cells in one cell, separated by coma.
Please refer below example, all the values are sorted and unique. Can anyone please help me in this.
I tried writing a loop for this but not able to write it correctly.

Input

A1 = Yamaha
A2 = Honda
A3 = Suzuki

Output B1 = Yamaha, Honda, Suzuki

 

[Chihiro]

Something like this?

Sub Test()
Dim lRow As Long
Dim oString As String
Dim cel As Range
lRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
oString = ""
For Each cel In Sheet1.Range("A1:A" & lRow)
    If cel.Row = lRow Then
        oString = oString & cel.Value
    Else
        oString = oString & cel.Value & ", "
    End If
Next cel
Sheet1.Cells(1, 2).Value = oString
End Sub

 

[shrivallabha]

Do you want to do this for learning loop or just need the result Concatenated?

If not then you can do this without loop like below

Public Sub Concat()
Range("B1").Value = Replace(Application.Trim(Join(Application.Transpose(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value), " ")), " ", ", ")
End Sub

 

[jindon]

UDF,
This concatenates cells that is not empty.

Use in cells like
=myJoin(A1:A3,", ")

Function myJoin(rng As Range, joinStr As String) As String
    myJoin = Join(Filter(rng.Parent.Evaluate("transpose(if(" & rng.Address & "<>""""," & rng.Address & ",char(2)))"), Chr(2), 0), joinStr)
End Function

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. It is working great ! In my code, I was replacing Rng with Rng itself. Hence got confused.

Your use of "oString = oString & cel.Value & ", "" is something which I could not think of.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @shrivallabha thanks lot. No, this is a genuine requirement. I ma checking your code, will revert with details. Good night.