1

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
    Else
        s = s & "," & Cells(i, 1).Value
    End If
    i = i + 1 
Loop

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
Mike
Tony
Dave


Topic 2 
Steve
Joe
Sally


Topic 3
...

The desired output would be:

Mike, Tony, Dave

(in clipboard)

Then on the next iteration:

Steve, Joe, Sally

(in clipboard)

Community
  • 1
AllInOne
  • 113

1 Answers1

2

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
Next

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

Exit Sub
Errhndl:
MsgBox "Can't use this macro if nothing is selected or a non-cell object is selected"
Exit Sub
Errclip:
MsgBox "Error copying text to clipboard! Text was: " & output
End Sub
allquixotic
  • 34,882