如何避免在Excel中垂直合并单元格？ [英] How do I avoid merging cells vertically in excel?

问题描述

我厌倦了合并的单元格。他们只是不工作。

所以我发现一种方法来避免水平合并单元格，当我只需要我的文本以多列为中心。那就是： Range（A1：C1）。Horizo​​ntalAlignment = 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屋！