Link to home
Start Free TrialLog in
Avatar of jcgroove
jcgroove

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jcgroove
jcgroove

ASKER

I changed cell1 to cell4 and the code works perfectly.
Thanks!!
Great, glad that helped.

~bp