Timesaving macro concatenates cell values
While working with spreadsheet data, it’s common to face situations where you have to manually reorder or transform data. Macros are of good help then. I’ll share an example of a quite common case where simple macro can help save a lot of time.
Case: the spreadsheet contains manually entered and badly structured data. You need to paste together the data from various cells, but you can’t do it with formulas because every situation is slightly different (contains different number of rows e.g.). Basically, you need a tool that takes the contents of currently selected cells, concatenates the values and pastes the outcome somewhere.
Problem and the desired outcome is depicted on the following screenshot.
As a solution, you can use a simple macro that takes the contents of the selection, concatenates it using newlines between values and pastes the outcome to the first selected cell. The code that does this is on the following screenshot. It uses Selection.Cells property that refers to the currently selected range of cells.
Here is the code for copy/pasting.
Sub Macro1()
Dim s As String
Dim i As Integer
i = 0
For Each Cell In Selection.Cells
If (i > 0) Then
s = s & Chr(10) & Cell.Value
Else
s = Cell.Value
End If
Cell.Value = “”
i = i + 1
Next
Selection.Cells(1, 1).Value = s
End Sub
doesn’t work
move all to one cell , without coma
should be :
s = s & Chr(44) & Chr(10) & Cell.Value