Adjust RowHeight progammatically based on number of lines of text in a cell

  • Thread starter Thread starter Suh Suk Ho
  • Start date Start date
S

Suh Suk Ho

Dear Colleagues;

At least, I belived that should be possible.

I have a cell that will be filled with large text with a lot of
vbCrLf(meaning I pressed "Enter" to change lines).

In order to adjust the height, I tried to search for "vbCrLf" using
instr("cell text", vbCrLf). And using returned value, I tried to multiply a
RowHeight.

But the "instr" function doesn't return a correct value. Maybe because the
cell is a "wrapped text" cell or I don't know.

Do you know how to do this?

Then let's share it...

Suh, Suk Ho
 
Dear Mike;

Thank you for your kind help.

I tried your code at it works at the code just fine.

But if I replace MyText to MyText=sheets("quotation").Range("a1").Value,
then Mid(MyText, a, 2) always returns 0.

The cell, "a1" contains a long text in multiple lines(made by pressing
Shift+Enter). So, I believed that Shift+Enter is a vbCrLf. Isn't this true?

Please help me again.!!

Sincerely,

Suh, Suk Ho
 
I don't think you hit the enter key or shift-enter. I bet you hit alt-enter.

And you might just want to make sure the cell has wraptext turned on. Then
autofit the row.

Option Explicit
Sub testme01()
With ActiveSheet
With .Rows("7:11")
.WrapText = True
.AutoFit
End With
End With
End Sub

I think I'd look at the columnwidth, too. Make sure it's not too wide after you
turn wraptext on.

You can record a macro to get the code when you do it once manually.
 
Back
Top