I have some data in an excel spreadsheet that I need to reformat. The existing format is in rows. The desired format is comma separated.

Elsewhere on superuser I found a VBA script that gets me about 75% of the way there...

Convert a column into a comma separated list

Sub generatecsv() 

Dim i As Integer
Dim s As String

i = 1

Do Until Cells(i, 1).Value = ""
    If (s = "") Then
        s = Cells(i, 1).Value
        s = s & "," & Cells(i, 1).Value
    End If
    i = i + 1 

Cells(1, 2).Value = s

End Sub

The shortcomings of this script for my intended workflow currently are:

The range of cells must always be in column 1. I need to have the script take input from a selected range anywhere in the worksheet.

The output of the script always appears at Cell(1,2). I'd like to have it copied to the clipboard.

I was hoping that something like s.Copy or s.PutInClipboard would work but am getting errors.

Data looks like this:

Topic 1

Topic 2 

Topic 3

The desired output would be:

Mike, Tony, Dave

(in clipboard)

Then on the next iteration:

Steve, Joe, Sally

(in clipboard)

1 Answer 1


First, in your workbook containing the macro, in the VBA IDE, go to Tools -> References and add the reference to "Microsoft Forms 2.0 Object Library" (the file path on my system is C:\Windows\system32\FM20.DLL.)

Then try this macro. This macro will put the data that is "selected" (highlighted) on the clipboard. It will go left to right if you select a series of cells in the same row; it will go top to bottom if you select a series of cells in the same column.

If you need to do a composite of both rows and columns (a rectangle shaped selection of more than one row and more than one column), you can add in code like:

MsgBox "Row: " & cell.Row & " Column: " & cell.Column

in order to determine which row numbers (1-based) and column numbers (A=1) are being enumerated, and in which order.

If you need something that will put the data on the clipboard, then wait for a user action, then put the next set of data on the clipboard automatically, this macro would have to be modified. It isn't clear from your question what exactly you mean by "selected".

Sub su492198()
Dim cell As Range
Dim sel As Range
Dim output As String
Dim first As Boolean
Dim dat As New DataObject

first = True
output = ""

On Error GoTo Errhndl
Set sel = Selection
On Error GoTo 0

For Each cell In sel.Cells
    If first = False Then output = output & ", "
    output = output & cell.Value
    first = False

On Error GoTo Errclip
dat.SetText output
On Error GoTo 0

Exit Sub
MsgBox "Can't use this macro if nothing is selected or a non-cell object is selected"
Exit Sub
MsgBox "Error copying text to clipboard! Text was: " & output
End Sub
  • Thanks, will try this now. My 'selected' range can be expected to be contiguous rows in a single column.
    – AllInOne
    Oct 24, 2012 at 15:29
  • This macro only handles "contiguous rows" and has no functionality for iterating through groups of values with a header and a blank line between them and automatically putting each group on the clipboard. If you need that, let me know, although it would complicate the macro. Oct 24, 2012 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .