Macro Convert Row Value to Column Value with Comma

Dear Admin,

I got a coding as below, it is work fine but I crack my head also can't think of how to amend it to suit my need. My coding work as example below,

Example:
Initial in A1:A

Row 1 100
Row 2 200
Row 3 300
Row 4 400
Row 5 500

Result:

Col1 Col2 Col3 Col4 Col5
100 200 300 400 500

Sub Txt_To_Rows()
     
    Dim arrText() As String
    Dim varItm As Variant
    Dim rngText As Range
    Dim rngCl As Range
    Dim i As Integer
    Dim j As Integer
     
    Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    j = 2
     
    For Each rngCl In rngText
        arrText = Split(rngCl)
        i = 1
        For Each varItm In arrText
            Cells(i, j) = varItm
            i = i + 1
        Next varItm
        j = j + 1
    Next rngCl
     
End Sub

However, I wish to add comma sign can convert it into one single cell. Is It possible ?

Result is

100,200,300,400,500


Even though I canno't convert all in one cell can 255 character limitatiton, at least I can make it auto add comma sign for me , any idea??? Thanks in advance .

Hi Kenji, I think you could just use:

Sub conv()
Dim i As Long, myStr As String, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    myStr = myStr & ", " & Cells(i, 1).Value
Next i
Range("A1:A" & LR).ClearContents
Range("A1").Value = Right(myStr, Len(myStr) - 2)
End Sub