Why you should not merge cells

Many experienced members recommend against merging cells. I am compiling a little guide as to why merging cells is a terrible idea. I am interested in your contributions and comments.

Here are some things off the top of my head:

1. Losing the ability to properly sort data
2. Losing the ability to run VBA programming code on your data because it doesn't handle merged cells very well (code may not be able to operate on a single cell if it is part of a merged cell; can hamper loops), and a significantly larger amount of code may need to be written to take into account the merged cells
3. Losing the ability to easily copy from and paste elsewhere, or paste to your worksheet.
4. Cannot select a column if the first row has a merged cell
5. Cannot select cells in a column by dragging if the range includes a merged cell that extends into other columns
6. Cannot select cells in a row by dragging if the range includes a merged cell that extends into other rows
7. In VBA the Range.Find function will not find a value in a merged cell if you search a row or column , even if the merged value is in that row or column

Gosh, I'd forgotten about that one! Yes, one common mistake that leads to the spawn of Satan, aka merged cells, is the idea that the aesthetics of the worksheet are more important than the data processing (often because the user doesn't realise that Excel is not a page layout program).

I blogged about this some time ago: https://excel.solutions/2016/10/usin...ctively-excel/

8. VBA can't count correctly the number of specialcells:

Please Login or Register  to view this content.

9. You can also have values in the merged cells that you can't see but that Excel will include in calculations, depending on how you merged the cells.

10. advancedfilter will produce unpredictable results

Originally Posted by rorya

9. You can also have values in the merged cells that you can't see but that Excel will include in calculations, depending on how you merged the cells.

This is a new one on me. Whenever I have merged cells, I was warned that the upper-left cell will be retained but data in other cells will be lost. How can you merge cells without losing values in the cells you can't see?

Me, too, Jeff - how would one circumvent this?

If you use the format painter, all cells retain their values. Here's an example. A1:B1 merged with the format painter, A2:B2 with normal methods. Both A cells had 1 in and both B cells had 2 in before the merge.

Originally Posted by rorya

If you use the format painter, all cells retain their values. Here's an example. A1:B1 merged with the format painter, A2:B2 with normal methods. Both A cells had 1 in and both B cells had 2 in before the merge.

Well I'll be darned. There are a handful of oddball things like that which Microsoft should have cleaned up years ago.

Too much of a niche case to waste resources on, I'd have thought. Far better to keep inventing new lookup functions...