The selection contains multiple data values… Merging cells in Excel.

This is a super powerfeature I have always missed in Excel: the ability to join the content of cells without losing any data. Sorry, what’s that?? Yes, I know about the ‘merge’ option. It’s lame! Try merging two cells that both have content. Excel answers, delightfully happy, that you will only keep data in the first cell and lose all of the data of the other cells, and go deal with it.

multiple-data
multiple-data

So, I decided to ‘deal with it’ and I present here several macros for your pleasure and entertainment that will solve this once and for all.

Merging, Joining and Combining

Well, that’s all the same of course. Let’s start with a simple macro to join a bunch of cells and put the result in the first cell.

Sub Join()
   Dim out As String
   Dim c As Range

   out = ""
   For Each c In Selection.Cells
      out = out & c.Value
      c.ClearContents
   Next
   Selection.Cells(1, 1).Value = out
End Sub

How does it work? First, we define the variable out which will contain all content. Then we loop over all cells in the current selection and we combine their values using the concatenation operator “&”. Finally we put the value of out in the first cell of the selection.

If you have never created a macro before, you can add them by pressing ALT+F11. After that, make sure to copy this one to the personal macro workbook.

In some occasions I needed to merge, but also keep the data separated by either commas or newlines. So I made a slightly modified version with arguments. Note for noobs: you can’t run these like normal macros, you’d have to go into the VB editor and start them from the immediate window. Or, you can make a short calling-macro similar to the JoinWithBreaks below.

' join the contents of a selection of cells
' and put all of these together in one single cell
Sub Join(Optional s As String = "", Optional wrap As Boolean = True)
    Dim out As String
    Dim c As Range
    Dim n As Integer

    out = ""
    For Each c In Selection.Cells
        n = n + 1
        out = out & c.Value
        If n < Selection.Cells.Count Then
            out = out & s
        End If
        c.ClearContents
    Next
    Selection.Cells(1, 1).Value = out
    Selection.Cells(1, 1).WrapText = wrap
End Sub

Sub JoinWithBreaks()
    Join vbNewLine, True
End Sub

Fillerup

Another problem closely related to the merge problem is when you have a sheet looking like this:

excel fill problem
excel fill problem

And let’s say it extends a long long way to the bottom for your archive of ten years. To create a usable Excel table and e.g. to create a pivot table your table needs to have values in every cell, not just the first one. Humans can reason that cell A3 also belongs to January, but computers and thus Excel cannot. Clicking on the autofill handle in cell A2 fixes this, but doing so for 100 cells is still very tedious work. For that, we can use the following macro, which will automagically fill every empty cell with the value right above it.

Sub Filler()
    Dim c As Range

    For Each c In Selection.Cells
        If IsEmpty(c) And c.Row <> 1 Then
            c.FormulaR1C1 = c.Offset(-1, 0).FormulaR1C1
        End If
    Next
End Sub
Advertisements

One thought on “The selection contains multiple data values… Merging cells in Excel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s