When you want to merge values from multiple cells into a single cell, the usual solution is to use the CONCATENATE function or the ‘&’ ampersand symbol in a formula.
[image]
This is perfect for setting up arrays of formulas to re-use with changing cell data.
But there are times when you might want to be able to instantly merge Excel cell data on the spot, without the fuss of typing in formulas and then clicking copy.
You can add a macro button to your Excel Quick Access Toolbar, or to the Excel Ribbon to do this for you. It will merge a selected range of cells and copy the result to the clipboard. So it’s ready to paste directly where you need it.
Sub range_concat_space()
Dim rng As Range ‘the selected range
Dim thiscell As Range ‘next cell in the selection
Dim bigstg ‘concatenated string
bigstg = “”
Dim clip_data As DataObject
Set clip_data = New DataObject
Set rng = Selection
For Each thiscell In Selection
bigstg = bigstg & ” ” & thiscell
Next
‘append clipboard to end of selected cell contents
clip_data.SetText bigstg
clip_data.PutInClipboard
‘optional message:
message = MsgBox(“Copied to clipboard: ” & bigstg, vbOKCancel, “Concatenate range macro”)
Select Case message
Case vbCancel
End
End Select
‘copy_data = Replace(copy_data, Chr(34), “”)
End Sub
Sub range_concat_newline()
Dim rng As Range ‘the selected range
Dim thiscell As Range ‘next cell in the selection
Dim bigstg ‘concatenated string
bigstg = “”
Dim clip_data As DataObject
Set clip_data = New DataObject
Set rng = Selection
For Each thiscell In Selection
bigstg = bigstg & thiscell & Chr(13)
Next
‘append clipboard to end of selected cell contents
clip_data.SetText bigstg
clip_data.PutInClipboard
message = MsgBox(“Copied to clipboard: ” & bigstg, vbOKCancel, “Concatenate range macro”)
Select Case message
Case vbCancel
End
End Select
‘copy_data = Replace(copy_data, Chr(34), “”)
End Sub
Learn how to add macro buttons to your Quick Access Toolbar in Excel
To learn how to enable macros to run, and how to save the above VBA macro code to your Excel, please download my e-book on Amazon, Making the Most of the Quick Access Toolbar in Excel, for step by step directions:
