Separate Values with Comma

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
590
Office Version
  1. 2019
Platform
  1. Windows
I have sets of data that i can put into an engine, the engine needs the values separated with a comma. How can I copy a section of cells and when i copy them, they are copied as a set, and separated by a comma? That way i dont have to do it manually, especially when i want to copy over 50+ of them.

1619025128949.png


thank you in advance.
 
If all you want is to put this string onto your clipboard for pasting elsewhere, then:

In your workbook module:

VBA Code:
Private Sub Workbook_Open()
Run "MakeMenu"
End Sub

Private Sub Workbook_Activate()
Run "MakeMenu"
End Sub

Private Sub Workbook_Deactivate()
Run "RightClickReset"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightClickReset"
End Sub

In a standard module:

VBA Code:
Private Sub RightClickReset()
On Error Resume Next
CommandBars("Cell").Controls("CopyVisibles").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub

Private Sub MakeMenu()
Run "RightClickReset"
Dim cb As CommandBar, MenuObject1 As CommandBarButton
Set cb = Application.CommandBars("Cell")
Set MenuObject1 = _
cb.Controls.Add(Type:=msoControlButton, before:=1)
With MenuObject1
.Caption = "CopyVisibles"
.OnAction = "KoppyViz"
End With
Application.CommandBars("Cell").Controls(2).BeginGroup = True
Set cb = Nothing
Set MenuObject1 = Nothing
End Sub

Sub KoppyViz()
Dim cell As Range, strVal As String
For Each cell In Selection.SpecialCells(12)
strVal = strVal & cell.Value & ","
Next cell
strVal = Chr(39) & Left(strVal, Len(strVal) - 1)
Dim DatObj As New DataObject
DatObj.SetText strVal
DatObj.PutInClipboard
End Sub

Note that I added the apostrophe character (ascii 39) because if you were to paste these numbers into a cell, Excel will think you are trying to enter a large number greater than 15 digits and hence you will paste a scientific notation that will be useless. Of course if the data will be text, you do not need the preceding apostrophe character.

After you install the code as I directed, save the workbook, and either...
...close the workbook and reopen it
or
...activate a different workbook in that same instance of Excel and then reactivate the workbook containing this code.

Then, right click any cell in that workbook and the first item on the right-click menu will be a button captioned "CopyVisibles" Just click that button after you selected whatever cells you want to build this comma-delimited string from, and the values in the visible cells of that selected range will be the string on your clipboardto be pasted later.

From there, hitting Ctrl+V would paste the string.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If all you want is to put this string onto your clipboard for pasting elsewhere, then:

In your workbook module:

VBA Code:
Private Sub Workbook_Open()
Run "MakeMenu"
End Sub

Private Sub Workbook_Activate()
Run "MakeMenu"
End Sub

Private Sub Workbook_Deactivate()
Run "RightClickReset"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightClickReset"
End Sub

In a standard module:

VBA Code:
Private Sub RightClickReset()
On Error Resume Next
CommandBars("Cell").Controls("CopyVisibles").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub

Private Sub MakeMenu()
Run "RightClickReset"
Dim cb As CommandBar, MenuObject1 As CommandBarButton
Set cb = Application.CommandBars("Cell")
Set MenuObject1 = _
cb.Controls.Add(Type:=msoControlButton, before:=1)
With MenuObject1
.Caption = "CopyVisibles"
.OnAction = "KoppyViz"
End With
Application.CommandBars("Cell").Controls(2).BeginGroup = True
Set cb = Nothing
Set MenuObject1 = Nothing
End Sub

Sub KoppyViz()
Dim cell As Range, strVal As String
For Each cell In Selection.SpecialCells(12)
strVal = strVal & cell.Value & ","
Next cell
strVal = Chr(39) & Left(strVal, Len(strVal) - 1)
Dim DatObj As New DataObject
DatObj.SetText strVal
DatObj.PutInClipboard
End Sub

Note that I added the apostrophe character (ascii 39) because if you were to paste these numbers into a cell, Excel will think you are trying to enter a large number greater than 15 digits and hence you will paste a scientific notation that will be useless. Of course if the data will be text, you do not need the preceding apostrophe character.

After you install the code as I directed, save the workbook, and either...
...close the workbook and reopen it
or
...activate a different workbook in that same instance of Excel and then reactivate the workbook containing this code.

Then, right click any cell in that workbook and the first item on the right-click menu will be a button captioned "CopyVisibles" Just click that button after you selected whatever cells you want to build this comma-delimited string from, and the values in the visible cells of that selected range will be the string on your clipboardto be pasted later.

From there, hitting Ctrl+V would paste the string.
I am getting the following error on this line:

Dim DatObj As New DataObject

"User-defined type not defined"
 
Upvote 0
Forgot to mention, not knowing your familiarity with VBA, you need to establish a connection to the Microsoft Forms 2.0 Object Library. In the VBE, click Tools > References, select that reference, and click OK. After that the code will work. The ref is required because the clipboard is set to be a Data Object not specific to just Excel but to Windows.
 
Upvote 0
Forgot to mention, not knowing your familiarity with VBA, you need to establish a connection to the Microsoft Forms 2.0 Object Library. In the VBE, click Tools > References, select that reference, and click OK. After that the code will work. The ref is required because the clipboard is set to be a Data Object not specific to just Excel but to Windows.
ok, got that setup, i selected 10 cells, went to a blank cell to right click and the selected cells went away, how do i bypass that need?
 
Upvote 0
Right-click somewhere in the selection and click the dedicated button on the right click menu. That is in acordance with this code line:
VBA Code:
For Each cell In Selection.SpecialCells(12)
From there you can go anywhere you want to paste the data.

If for some reason you are compelled to have another cell or worksheet activated at the precise moment you want the data copied, then that is something you need to explain so the code can be written with that consideration in mind. As it is, I assumed, based on your picture, that you have selected the range of interest, so that is how I wrote the code I posted.
 
Upvote 0
Solution
This UDF will put the string in whatever cell you enter the formula =CommaMeeya() in.
VBA Code:
Public Function CommaMeeya() As String
  Dim cell As Range, strVal As String
  For Each cell In Range("A2:A25").SpecialCells(12)
    strVal = strVal & cell.Value & ","
  Next cell
  strVal = Left(strVal, Len(strVal) - 1)
  CommaMeeya = strVal
End Function
I know this thread moved on to a more complex implementation, but I wanted to point out to the readers of this thread that as long as the range is a vertical selection down a single column (you can use a specific range like Tom did in his original function if desired), the above function can be written as a one-liner...
VBA Code:
Public Function CommaMeeya() As String
  CommaMeeya = Join(Application.Transpose(Selection), ",")
End Function
 
Upvote 0
I know this thread moved on to a more complex implementation, but I wanted to point out to the readers of this thread that as long as the range is a vertical selection down a single column (you can use a specific range like Tom did in his original function if desired), the above function can be written as a one-liner...
VBA Code:
Public Function CommaMeeya() As String
  CommaMeeya = Join(Application.Transpose(Selection), ",")
End Function
thank you Rick
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top