Hi, I am not an excel expert but i was able to get this macro to work with a couple of minor problems.
a) how do i remove the last comma from line 6 (40 instead of 40,)
b) Can i remove the last comma and "-" from any other line (line 3 for example)
Thank you for your time
1,2,3,4,5,6,7, -
8,9,10,11,12,13,14, -
15,16,17,18,19,20,21, -
22,23,24,25,26,27,28, -
29,30,31,32,33,34,35, -
36,37,38,39,40,
Sub Test()
Dim a As String, b, c, d As Long
a = "'" & CStr(Cells(40, 2).Value) & ","
b = 3
c = 6
d = 1
While Not IsEmpty(Cells(40, b))
a = a & CStr(Cells(40, b).Value) & ","
b = b + 1
d = d + 1
If d >= 7 Then
a = a & " -"
Cells(c, 53).Value = a
c = c + 1
a = ""
d = 0
End If
If c > 10 Then
Cells(c, 53).Value = a
End If
Wend
End Sub
Bookmarks