How do I avoid merging cells vertically in excel?

Question

I'm tired of merged cells. They just don't work.

So I found a way to avoid merging cells horizontally, when I just need my text to be centered across multiple columns. That's: Range("A1:C1").HorizontalAlignment = xlCenterAcrossSelection.

It works like a charm. It's perfect. It's just what I needed.

I oh-so-naively thought you could do the same vertically, for centering text over multiple rows. Something like: Range("A1:A3").VerticalAlignment = xlCenterAcrossSelection.

But alas, that doesn't work.

Am I missing something here, or did Excel developers really think that, while centering across columns is a common deal, centering over rows is so much of a silly idea nobody would ever do that so why even bother?

Answer 1

You can try this:

Sub VerticalAlign()
ThisWorkbook.Sheets(1).Cells(Round((WorksheetFunction.CountA(Range("A1:A6")) + WorksheetFunction.CountBlank(Range("A1:A6"))) / 2, 0), "A") = "Your Value Here"
End Sub

You'll need to change the ranges being counted, and the column it's referring to, but that should just about do it. A little warning, though: VBA does NOT round the same way that Excel does. It should always be within one digit, though. You can read more about that here: http://support.microsoft.com/kb/194983

Answer 2

I found a happy accident that works around my issue on this if anyone cares to know.

For my situation I needed to track employee vacation time on a linear calendar that was sort-able by name and responsible area. The title section covered three rows that i wanted visible when the data below was filtered. Row 1 = years (merged across months), Row 2 = months (merged across days), Row 3 = days. Cells A1:A3 was merged and labeled Names (employees), B1:B3 merged and labeled AREA (responsibilities) so we could track vacation time on a calendar spreadsheet to make sure each area had coverage (at least one employee responsible for it was available). The downside of this method caused the filter blocks to be associated with row 1, so row 2 and 3 were hidden when names were filtered on.

Then I tried the VBA code from previous post and it worked, but maxed out at font size that would fit, if too big it would be cut off. So Aesthetically, it looked bad.

Then I put the Names/Area titles in cells A3 and B3 without merge and set filters on row three. This filtered everything starting at row 4 effectively without hiding anything above it, but still looked bad.

Now I found the work around. If you have your titles in the top cell of where you want to merge, and some value in the bottom section. Set filters on the bottom section (need a value to set filters). Then merge the three cells, it works. So now my A1:A3 Name, and B1:B3 Area is merged, but the filter boxes are on the bottom of the sections (aligned with row 3).

Hopefully that works for some people. I then locked those cells from edit (except auto filter settings) and now it cant be changed accidentally. only filtered.

Answer 3

We use textbox shapes over cells to do this. If you hold Alt while drawing out the shape, the corners will snap to the Excel grid and move and size with the cells, so it doesn't take much more time than typing into the cells.