vba 通过vba计算包裹单元格中的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9902653/
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
Count number of lines in a wrapped cell through vba
提问by Sarika.S
How can I count the number of lines in a wrapped cell through vba code?
如何通过 vba 代码计算包装单元格中的行数？
With Cells(1, 1)
.WrapText = False
height1 = .height
.WrapText = True
height2 = .height
End With
MsgBox height2 / height1 & " Lines"
This code will not work since I have set row height to a fixed one (only one line is visible).
此代码将不起作用，因为我已将行高设置为固定的行高（只有一行可见）。
Also in my text, no line breaks since the data is entered through VBA code.
同样在我的文本中，没有换行符，因为数据是通过 VBA 代码输入的。
Thanks in advance.
提前致谢。
回答by brettdj
One way would be to subtract the length of the cell with linebreaks removed from the length of the unadjusted cell
一种方法是从未调整的单元格的长度中减去带有换行符的单元格的长度
Linebreaks can be replaced with a 0 length string using the worksheet function Substitute
可以使用工作表函数将换行符替换为 0 长度的字符串 Substitute
Sub test()
Dim c As Range
Set c = ActiveCell
MsgBox Len(c.Value) - Len(Application.WorksheetFunction.Substitute(c.Value, Chr(10), vbNullString)) & " Linebreak(s)"
End Sub
[Update; Not a linebreak!]
[更新; Not a linebreak!]
As Sid points out in Get first two lines of text from a wraped cell in excelthis is tricky if working with font sizes (which can change).
正如 Sid 在Get first two lines of text from a wrapped cell in excel 中指出的那样，如果使用字体大小（可以更改），这很棘手。
I think the most foolproof way would be to copy the cell contents elsewhere (to an otherwise blank row), and autofit that row based on that cell to get the height.
我认为最简单的方法是将单元格内容复制到其他地方（到其他空白行），并根据该单元格自动调整该行以获得高度。
回答by thanos.a
Let's say you want to count the number of lines in cell A1, you can do this:
假设您要计算单元格 A1 中的行数，您可以这样做：
' Gets the value of the cell (shortcut)
val = Range("A1").value
' Counts the lines by comparing the contents by the themselves
' after removing the line brakes which is character 10.
' This gives back the number of breaks
' so we add 1 to get the number of lines,
' since the last line doesn't have a line break.
lines = Len(val) - Len(Replace(val, Chr(10), "")) + 1
