Merge and Center warning!

Formatting issue

By Neale Blackwood

Ever had an error message which stated that you couldn’t do something to a merged cell? Well the merge format is the culprit. The Merge and Center tool on the toolbar is commonly used to format headings to make them look good by centering a heading across cells below it. The effect on macros and on copying can be disastrous. Normally stable macros can crash if they have to copy and paste ranges that contain Merged cells.

Excel limits what you can do with merged ranges because when you merged a range you in effect remove cells from the sheet. As an alternative that does not crash macros or display error messages you can use Center Across Selection which achieves the same effect.

In all versions of Excel Center across Selection is in the Format Cells dialog in the Alignment tab under the Horizontal drop down list. Ctrl + 1 will open the Format cell dialog in all versions of Excel.