Is there a way to automate cell merging in excel?

I have something like this:

The outer (left and top) cells show the pattern, according to which I would like to merge cells between them.

Is there a way to automate the cell merging process, instead of tedious highlight + F4 method?

3 Answers

I don't believe there's a shortcut to shifting selections one column to the left or right, which is why merging cells repeatedly can be a tedious task.

You can, however, write a macro for that and then assign a shortcut key to it (Ctrl + Shift + X, for example).

You can then use the key sequence for merging cells, which is Alt + H, M, M.

Here's the code:

Sub MoveSelectionRight() Set r = Selection r.Offset(0, 1).Resize(r.Rows.Count, r.Columns.Count).Select
End Sub

Here's another one for shifting selections one column to the left (and then maybe assign it to Ctrl + Shift + Z):

Sub MoveSelectionLeft() Set r = Selection r.Offset(0, -1).Resize(r.Rows.Count, r.Columns.Count).Select
End Sub

You can merge one column, and then use the fill handle (little square on the bottom right of the selected cell) to fill the formatting sideways so the cells on the right of it also merge too.

Yes, there's an easier way:

1. Select column A (Ctrl-Space)
2. Copy (Ctrl-C
3. Select column B (Left, Ctrl-Space)
4. Paste Special Formats (Alt-E-S-T-Enter)