vba 行高自动调整不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12132726/
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
Row Height AutoFit doesn't work
提问by Alegro
So, today is my bad day
I discovered that excel row AutoFit - doesn't work
所以，今天是我糟糕的一天，
我发现 excel 行 AutoFit - 不起作用
a = "Please"
b = "Do AutoFit"
range01.Value = a & vbNewLine & b
range01.EntireRow.AutoFit // First and last line are horizontally cutted
Chr(10) instead of vbNewLine - doesn't help
It works only if I write without line breaks:
Chr(10) 而不是 vbNewLine - 没有帮助
它只有在我没有换行符的情况下才有效：
range01.value = a & b
range01.EntireRow.AutoFit // works !!! Thanks to Microsoft !
回答by Joseph
Make sure that the cells that have line breaks in them have "Wrap Text" turned on. You can turn it on with VBA like so:
确保其中包含换行符的单元格已打开“换行文本”。您可以像这样使用 VBA 打开它：
Sub test1()
Dim a As String, b As String
a = "Please"
b = "Do AutoFit"
Range("A1").Value = a & vbNewLine & b
Range("A1").WrapText = True
Range("A1").EntireRow.AutoFit
End Sub
回答by Julius Getz M?rk
Autofit doesn't work on merged cells. My guess is that's what you are trying to do.
自动调整不适用于合并的单元格。我的猜测是这就是你想要做的。
Work around this by simulating the autofit on a single cell with the same width as your merged cell and set the height it gives you.
通过在与合并单元格宽度相同的单个单元格上模拟自动调整并设置它为您提供的高度来解决此问题。
Something like this:
像这样的东西：
Dim YourString As String, a As String, b As String
Dim TempLoc as Range
a = "Please"
b = "Do AutoFit"
YourString = a & vbNewLine & b
Set TempLoc = range01.offset(0, 10) ' Given the column 10 columns to the right has the exact same witdh as your merged cell.
' Autofit a single cell to get correct height
With TempLoc
.Value = YourString
.WrapText = True
.EntireRow.AutoFit
.RowHeight = TempLoc.RowHeight
.ClearContents
End With
回答by Todd
You could do:
你可以这样做：
Private Sub Worksheet_Activate()
Worksheets("Sheet1").Range("A:A").EntireRow.AutoFit
End Sub
回答by AjV Jsy
I solved an issue with this - I had a row with two merged cells both containing multi-line text with Chr(10) linefeeds - with Wrap set ON (but no lines actually wrap - only the Chr(10)s cause new lines). Autofit wouldn't work due to the merged cells.
我解决了一个问题 - 我有一行有两个合并的单元格，它们都包含带有 Chr(10) 换行符的多行文本 - Wrap 设置为 ON（但实际上没有换行 - 只有 Chr(10)s 导致新行） . 由于合并的单元格，自动调整不起作用。
The VBA workaround was to use a spare cell on the same row, and fill it with the same number of Chr(10) as found in one (either) of the multi-line cells. Then call AutoFit for that cell now containing the same number of invisible line feeds. Make surethe font and size are the same in the spare cell!
VBA 的解决方法是在同一行上使用一个备用单元，并用在一个（任一）多行单元中找到的相同数量的 Chr(10) 填充它。然后为该单元格调用 AutoFit 现在包含相同数量的不可见换行符。确保备用单元中的字体和大小相同！
' make C36 spare cell contain the same number of Chr(10) as D36, using a Repeat function :
' (after counting Chr(10) by comparing the length before and after substituting Chr(10) with "")
Worksheets(1).Range("C36").Value = _
Application.WorksheetFunction.Rept(Chr(10), _
Len(Worksheets(1).Range("D36").Value) - Len(Replace(Worksheets(1).Range("D36").Value, Chr(10), "")))
Worksheets(1).Range("C36").Rows.AutoFit
An alternative method for other scenarios could be to use an extra, hidden, worksheet - set a single cell to the same column width as your merged cells, copy the contents over, call autofit (which should work on the unmerged cell), then use the resulting rowheight from that one.
其他场景的另一种方法可能是使用额外的隐藏工作表 - 将单个单元格设置为与合并单元格相同的列宽，复制内容，调用自动调整（应该适用于未合并的单元格），然后使用从那个产生的行高。
回答by bryan s
One workaround is to shrink the column width down a bit for the column(s) that are causing the autofit problem, select all the rows you want to change, autofit the row height by double-clicking one of the row separators, and finally autofit the column(s) from the first step by double-clicking the column separator(s). Probably works best if only one or just a few columns consistently have the most text in their cells.
一种解决方法是将导致自动调整问题的列的列宽缩小一点，选择要更改的所有行，通过双击行分隔符之一自动调整行高，最后自动调整通过双击列分隔符从第一步中选择列。如果只有一列或几列在其单元格中始终具有最多的文本，则可能效果最佳。
