Archive for November, 2013|Monthly archive page
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.
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
Another problem closely related to the merge problem is when you have a sheet looking like this:
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