donderdag 21 februari 2008

Merging-cells nightmare and its solution

I've gone through a stupid nightmare merging (combining) a continuous series of cells into one cell.

Suppose there's an empty worksheet and the selected cell is A1

Do the following:

1. Type something into cell A1
2. Select the following cells A1, B1, C1 and D1
3. Locate the button Merge cells into one

Changes are great that absolutely nothing happens.

I've repeated this using the macro recorder in order to see what happens under water and I've learned my lesson:

The instruction 'Range("A1, B1, C1, D1").Select' gets generated when the CTRL key is pressed while selecting cells. The latests cell looks like to be in an edit state. This kind of selection fails the merge! You find a selection of loose cells separated with commas. Although consequent, Excel interprets this if it wasn't, since the merge function doesn't work.

The instruction 'Range("A1:D1").Select' gets generated when the SHIFT key is pressed whilst clicking the cell D1. The latest cell is like the first one normally selected. This kind of selection succeeds the merge! Watch the colon which means a consequent area of cells and the merge works like a charm!

Now I'd like to share you the ultimate solution for mistakes in merging cells. I've thought for a short time that there was a bug in Office 2007!! Happily this thought is false!
When selecting cells don't ever touch the CTRL key or nothing will be merged! The right steps for a successful merge are:

1. Select the first cell of the region to be merged into one cell
2. Whilst pressing the SHIFT key select the last cell of the region
3. Now there a valid consequent selection, locate the merge-cells-into-one-cell function on the ribbon and use it

Geen opmerkingen: