Count number of lines in a wrapped cell through vba

Question

How can I count the number of lines in a wrapped cell through vba code?

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.

Thanks in advance.

If the cell text has line breaks then what Dave (Edit: brettdj) suggested below will work nicely. However if it is one continuous text then have a look at this thread. http://stackoverflow.com/questions/9889002/get-first-two-lines-of-text-from-a-wraped-cell-in-excel/9897363#comment12631761_9897363 — Siddharth Rout, Mar 28 '12 at 08:18

score 3 · Answer 1 · edited May 23 '17 at 11:56

3

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

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!]

As Sid points out in Get first two lines of text from a wraped cell in excel this is tricky if working with font sizes (which can change).

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.

edited May 23 '17 at 11:56

Community

1
1

answered Mar 28 '12 at 07:11

brettdj

54,857
16
114
177

This is similar to this question http://stackoverflow.com/questions/9889002/get-first-two-lines-of-text-from-a-wraped-cell-in-excel/9897363#comment12631761_9897363. Lot of things have to be considered if the text doesn't have line breaks (CHR10) +1 though for this method which will work for lines separated by line breaks :) – Siddharth Rout Mar 28 '12 at 08:16
1

@SiddharthRout Fair point. Although based on the same askers prior question http://stackoverflow.com/a/9902628/641067 I think it is linebreak related. – brettdj Mar 28 '12 at 09:04
In my text, no line breaks :( – skmaran.nr.iras Mar 28 '12 at 11:29

score 0 · Answer 2 · answered Dec 14 '21 at 19:11

If you need an approximation :

Sub approx_number_of_lines()
    Set Rng = Selection

    a = Len(Rng.Value)
    'number of line returns
    b = Len(Rng.Value) - Len(Application.WorksheetFunction.Substitute(Rng.Value, Chr(10), vbNullString))
    c = Rng.ColumnWidth
    d = (a / c) + b
    'd = WorksheetFunction.Ceiling(a / c, 1) + b
    Debug.Print d
End Sub

Count number of lines in a wrapped cell through vba

2 Answers2