How to AUTOMATICALLY autofit row height with wrapped text.

[Monia]

Hello everyone,

I have been reading all kinds of posts and websites for hours about this but I am unable to find what I am looking for... but I know it's possible because I have another document that does it (it happened by mistake and I have no clue how I did it!!).

My document is a template. When the user selects an item from a drop down list in cel E18, adjacent cells are auto populated with the info relating to the item selected. One of those cells is I18. The info in that cell is always much longer than the rest. So I set it to "wrap text".

So the document starts off empty.. so all the rows 14.25 in height. What I would like the document to do is auto adjust the height once an item is selected in E18 to properly display all the info in cell I18.

Like I said, I have another document that does it. And when I remove the item in E18, the row goes back to height of 14.25.

No macros please.

Anyone know how to do this?

 

[onlyadrafter]

Hello,

I know you didn't want a macro, but I'm not sure it can be done any other way.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 Then
        Range("I" & Target.Row).WrapText = True
        End If
End Sub

This code needs to go into the relevant Sheet Code window.

 

[Monia]

Thanks onlyadrafter. The reason I can't use macro is that this is a template that gets sent to others that do not allow macros.. so I have to find another way to do this. And I think I finally found it. It's so simple that I don't understand how this solution is not plastered all over the internet considering the amount of people looking for it..

Highlight the rows/columns in question, on the home tab in the cells section, click on format, then on format cells. Select the alignment tab. Then click "shrink to fit" FIRST, then "wrap text".

This works for me.. with only one strange quirk: it only adjusts the height after the data has been entered in a few cells. Hard to explain... but this is the closest I've come to a solution so it will have to do.

Unless anyone here has a better one

 

[Monia]

The solution was found in this thread: "Automatic Row Height for Wrapped Text", on this site.