Convert column list to comma separated list with VBA

 

Convert column list to comma separated list with VBA

If the CONCATENATE function is a little tedious for you, you can use a VBA to quickly convert the column list to a list in a cell.

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert column list to comma separated list

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

3. Click Run button or press F5 to run the VBA.

4. A dialog displayed on the screen, and you can select the column list you want to convert. See screenshot:

5. Click OK, then another dialog popped up for you to select a cell. See screenshot:

6. Click OK, and all values in the column list have been converted to a list separated by comma in a cell.

Tip: In the above VBA, "," indicates the separator you need, and you can change it as you need.