Excel Workshop Part 1

Data in a Column to a Single Cell Separated by Commas – Macro VBA

This macro is useful in case you have a data table in the following format and you want to transform it to a single cell separated by commas.

43680
43681
43659
43677
43578
43522

 

Preferred Results:

43680;43681;43659;43677;43578;43522

 

Excel Macro:

Option 1:

Sub ConcatColumnValues()
  Range("B1") = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ";")
End Sub

Option 2:

Public Sub TextEr()
rs = Rows.Count
lr = Cells(rs, 1).End(xlUp).Row
stxt = ""
For Each oj In Range("A1:A" & lr)
If stxt = "" Then
stxt = oj
Else
stxt = stxt & ";" & oj
End If
Next oj
Range("b1").NumberFormat = "@"
Range("b1") = stxt
End Sub