5

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?

Bruder
  • 563
  • 3
  • 8
  • 18
  • I am sorry but there is no standard way to center across vertically. As you can see when you pick a cell and open the Cell Format dialog. In the vertical alignment droplist, you have no option to center accross vertically. – JMax Mar 29 '12 at 13:37
  • Yeah, I thought that no "standard" way would be available. Maybe someone has a more elaborate workaround? – Bruder Mar 29 '12 at 13:59
  • Apart from using a rectangular shape that would lie over the "merged cells", I can't see any Excel way, I would handle this with standard merging and its drawbacks – JMax Mar 29 '12 at 16:30
  • 1
    throwing another comment out here.... if you could get the count of the rows in the range and then place the text in the middle cell ... you'd have to pick a middle ground for ranges with even row counts. – Scott Holtzman May 14 '12 at 19:42

3 Answers3

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

PermaNoob
  • 849
  • 5
  • 17
0

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.

Chris Rae
  • 5,627
  • 2
  • 36
  • 51
0

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.

AdamTheHusband
  • 1