vba AutoFit 不适用于换行文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12541248/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
AutoFit doesn't work with wrapped text
提问by Alegro
I have one Chr(10) in the cell
我在细胞中有一个 Chr(10)
cell.WrapText = False
cell.EntireRow.AutoFit ' AutoFit works
' ------------ but:
cell.WrapText = True
cell.EntireRow.AutoFit ' AutoFit works only if the cell has less then five lines.
If I add some characters (one line more) - AutoFit doesn't work. Text is cutted on the first and last line.
ver - famous excel 2010
如果我添加一些字符（多一行）- AutoFit 不起作用。文本在第一行和最后一行被剪切。
ver - 著名的 excel 2010
回答by Siddharth Rout
It depends on how big the data is and what is the width of the column. Reason being the max height a row can expand is to 409.5 (546 pixels). If you manually increase the height of the row, you will notice that after a particular height, you will not be able to increase the height of the row. Check what is that height :) Same is the concept with the width. This is applicable to both rows and columns. In case of columns the max width is 254.86 (1789 pixels)
这取决于数据有多大以及列的宽度是多少。行可以扩展的最大高度的原因是409.5 (546 pixels). 如果您手动增加行高，您会注意到在特定高度之后，您将无法增加行高。检查高度是多少:) 宽度的概念也是如此。这适用于行和列。在列的情况下，最大宽度为254.86 (1789 pixels)
I used this code for demonstration purpose.
我将此代码用于演示目的。
Option Explicit
Sub Sample()
ActiveCell.WrapText = True
ActiveCell.EntireRow.AutoFit
MsgBox ActiveCell.RowHeight
End Sub
See this screenshot
看这个截图
This works
这有效
This doesn't
这不
The row has reach it's maximum. It cannot go beyond that.
该行已达到最大值。它不能超越那个。
The best way to handle this is to increase the width of the column.
处理此问题的最佳方法是增加列的宽度。
FOLLOWUP
跟进
The reason why it was not working is because you had multiple columns with data in that row and you were trying to just set the WrapText of 1 cell and hence it was not working. When you have multiple columns of data you have to use cell.EntireRow.WrapText = Trueinstead of ActiveCell.WrapText = True
它不起作用的原因是因为您在该行中有多个包含数据的列，并且您试图只设置 1 个单元格的 WrapText，因此它不起作用。当您有多列数据时，您必须使用cell.EntireRow.WrapText = True而不是ActiveCell.WrapText = True
回答by Qudsia
A related solution in case it helps someone. I have data with 'Wrap Text' on and line breaks in cells that come from copying data from formulas that used 'Char(10)'. I need the line breaks.
一个相关的解决方案，以防它帮助某人。我的数据带有“Wrap Text”，单元格中的换行符来自从使用“Char(10)”的公式复制数据。我需要换行。
For example, data in a cell should show like this:
City: New York (1950)
Country: USA
例如，单元格中的数据应显示如下：
城市：纽约 (1950)
国家：美国
But it showed something like this:
City: New
York (1950)
Country:
USA
但它显示的是这样的：
城市：
纽约（1950）
国家：
美国
Selecting the column and clicking to autofit resulted in slight improvement:
City: New York
(1950)
Country: USA
选择该列并单击以进行自动调整，结果略有改进：
城市：纽约
(1950)
国家：美国
Repeating manual auto-fit a 2nd time brings text to desired format.
If there is more space-separated text in a line, it takes that many more auto-fit attempts, and eventually format is fully corrected.
重复手动自动调整第二次将文本带到所需的格式。
如果一行中有更多以空格分隔的文本，则需要进行更多的自动调整尝试，最终格式会得到完全纠正。
So in my VBA code, I replicated this and repeated auto-fit code the number of times I knew would fix my issue:
所以在我的 VBA 代码中，我复制了这个并重复了自动适应代码我知道可以解决我的问题的次数：
'Adjust column Width
wsSched2.Columns("F:CZ").EntireColumn.AutoFit
DoEvents
wsSched2.Columns("F:CZ").EntireColumn.AutoFit
DoEvents
wsSched2.Columns("F:CZ").EntireColumn.AutoFit
(It may work without 'DoEvents'. I haven't tried)
（它可能在没有“DoEvents”的情况下工作。我没试过）
