Transpose data and insert a coma in between

[kittyfur]

Hello
I have a list of account numbers in Excel, like this:
123
234
345
456
567
678

Do you know how to code a macro so it can end up in ONE cell looking like this:
123,234,345,456,567,678

Thanks!
Jenny

 

[QuietRiot]

this will look down column A and then put the answer in B1

Sub Macro1()
Dim hey As String, num As String


Set C = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If C Is Nothing Then
    last = 1
Else
    last = C.Row
End If

For x = 1 To last

num = Cells(x, 1).Value
If x < last Then
hey = hey + num + ","
Else
hey = hey + num
End If
Next

Cells(1, 2).Formula = hey


End Sub

 

[QuietRiot]

hmm, i just realized you probalby don't know how to use a macro?

sorry I just whipped it up real quick im not sure how to do it non-macro

but hit Alt + F11 and paste that above code in there then exit.

make sure numbers are in A column and B1 is blank

then go to tools > macro> macros > and run Macro1