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