how to remove unused commas after concatenating

I have a column of data that I want to concatenate into a comma delimited string in a cell.
Problem is, using the code below, it leaves commas at the end of the string.
Additionally, after the sort, the data starts at cell one in the column instead of cell four.
I need the commas removed from the end of the string and the sorted data to start at cell four.

Please help!

Sub concgain()
    Dim c As Range, Str As String
    Range(Cells(1, 38), Cells(Rows.Count, 38).End(xlUp)).Select
    Selection.Sort Key1:=Range("AL4"), Order1:=xlAscending
    For Each c In Selection
        Str = Str & c & ","
    Next c
   Range("AO2") = Str
End Sub

Open in new window

jcgrooveAsked:
Who is Participating?
 
Bill PrewCommented:
Here's an approach to eliminate the trailing comma.

=> Edited to now also handle the row 4 start...

Sub concgain()
    Dim c As Range, Str As String
    Range(Cells(4, 38), Cells(Rows.Count, 38).End(xlUp)).Select
    Selection.Sort Key1:=Range("AL4"), Order1:=xlAscending
    Str = ""
    For Each c In Selection
        If Str = "" Then
            Str = c
        Else
            Str = Str & "," & c
        End If
    Next c
   Range("AO2") = Str
End Sub

Open in new window

~bp
0
 
jcgrooveAuthor Commented:
I changed cell1 to cell4 and the code works perfectly.
Thanks!!
0
 
Bill PrewCommented:
Great, glad that helped.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.