如何避免在Excel中垂直合并单元格? [英] How do I avoid merging cells vertically in excel?
问题描述
所以我发现一种方法来避免水平合并单元格,当我只需要我的文本以多列为中心。那就是: Range(A1:C1)。HorizontalAlignment = xlCenterAcrossSelection
。
它的作用就像一个魅力。这是完美的。这只是我需要的。
我很天真地认为你可以垂直做相同的方式,以便将文本对齐在多行上。如下所示: Range(A1:A3)。VerticalAlignment = xlCenterAcrossSelection
。
但唉,这不行。我在这里缺少一些东西,或者Excel开发人员真的认为,以列为中心是一个常见的交易,以行为中心是一个愚蠢的想法,没有人会曾经这样做,为什么还要麻烦?
你可以试试这个:
Sub VerticalAlign()
ThisWorkbook.Sheets(1).Cells(Round((WorksheetFunction.CountA(Range(A1:A6))+ WorksheetFunction.CountBlank范围(A1:A6))/ 2,0),A)=您的价值在这里
结束子
您需要更改要计数的范围以及它所指的列,但这应该是这样做的。有一点警告:VBA并不像Excel那样做。它应该始终在一位数之内。您可以在这里阅读更多信息: http://support.microsoft.com/kb/194983
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?
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
这篇关于如何避免在Excel中垂直合并单元格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!