Excel Macros and Merged Cells

Merged cells are a problem

I don’t like merged cells. Here is one more reason.

The ClearContents command works for cells and ranges, but it doesn’t work for merged cells.

The command below will clear the entry from cell B2.

Range("B2").ClearContents

But if B2 is a merged cell it returns an error. Even if you can still select cell B2.

In all cases when you merge cells you lose at least one cell. In the image below cells C2 and D2 no longer exist, so if you try to refer to those cells your code you get an error.

Cell B2 still exists in the image above, but you still can’t use ClearContents on cell B2.

To clear entries in merged cells you have to use.

Range("B2").Value = ""

This is frustrating and yet another reason to avoid using merged cells.

The format to use instead of merged cells is called Center Across Selection.

The format is in the Alignment tab of the Format Cells dialog. Top drop down and the second last option. See image below.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.