vba Excel 宏：如何扩展 ROW 高度以容纳换行文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3619410/
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
Excel Macro: how do I expand a ROW height to accommodate wrap text?
提问by RocketGoal
I'm slowly amending and expanding an If...ElseIf...Else statement (see post) to help me format a long list of categories and sub-categories (thanks marg & Lunatik).
我正在慢慢修改和扩展 If...ElseIf...Else 语句（见帖子）以帮助我格式化一长串类别和子类别（感谢 marg 和 Lunatik）。
I've assigned fixed row heights to 90% of the range/lines. Now I'm stuck on those cells with lots of text that wraps over two lines in a cell. Two lines of text does not fit in my 10.5 standard height.
我已将固定行高分配给 90% 的范围/行。现在我被困在那些包含大量文本的单元格上，这些文本包含在一个单元格中的两行。两行文字不适合我的 10.5 标准高度。
I can't simply refresh the screen as the statement says any line that isn't exception one (bold), or exception two (superscript)should be 10.5pts. I need a third exception. I currently have:
我不能简单地刷新屏幕，因为声明说任何不是异常一（粗体）或异常二（上标）的行都应该是 10.5pts。我需要第三个例外。我目前有：
Sub setHeights()
Dim targetRange As Range
Dim targetCell As Range
Cells.Select
Selection.WrapText = True
Cells.EntireRow.AutoFit
Set targetRange = Range("B:B")
For Each targetCell In targetRange
If Not IsEmpty(targetCell) Then
If targetCell.Font.Bold Then
targetCell.RowHeight = 15
ElseIf targetCell.Characters(Len(targetCell), 1).Font.superscript Then
targetCell.RowHeight = 14
Else: targetCell.RowHeight = 10.5
End If
End If
Next targetCell
End Sub
Could I:
我可否：
- Find those targetCells with over 60 characters (the width of the fixed column)
- Apply .WrapText to those specific targetCells
- AutoExpand ONLY those targetCells (therefore not overwriting my standard 10.5pt lines for other non exception targetCells).
- 找到那些超过 60 个字符的 targetCells（固定列的宽度）
- 将 .WrapText 应用于那些特定的 targetCells
- 仅自动扩展那些 targetCells（因此不会为其他非异常 targetCells 覆盖我的标准 10.5pt 行）。
Would this work? Would it need to be placed in a separate SubRoutine because of the parameters of the first? What on earth would it look like? (see my embarrassing effort below)
这行得通吗？由于第一个的参数，是否需要将其放置在单独的子程序中？它到底会是什么样子？（见我下面的尴尬努力）
ElseIf targetCell.Characters(Len(TargetCell+60).TargetCell.WrapText Then
targetCell.Autofit
采纳答案by Dick Kusleika
This seems to work.
这似乎有效。
Sub setHeights()
Dim targetRange As Range
Dim targetCell As Range
Set targetRange = Range("B:B")
For Each targetCell In targetRange.Cells
If Not IsEmpty(targetCell.Value) Then
If targetCell.Font.Bold Then
targetCell.RowHeight = 15
ElseIf targetCell.Characters(Len(targetCell), 1).Font.Superscript Then
targetCell.RowHeight = 14
ElseIf Len(targetCell.Value) > 10 Then
targetCell.WrapText = True
targetCell.EntireRow.AutoFit
Else: targetCell.RowHeight = 10.5
End If
End If
Next targetCell
End Sub
回答by Bart
I don't get the whole thing. Do you want Excel to automatically adjust row height to amount of text ? Then your third 'exception' should be
我不明白整个事情。您是否希望 Excel 自动调整行高以适应文本量？那么你的第三个“例外”应该是
Else: targetCell.WarpText = true
