Dynamically resize row height

[troygeri]

I have a vlookup formula that returns variable text to a cell, some one line others up to 10 lines. I want to know how I can get the row height to automatically / dynamically adjust to accomodate the amount of text returned by the formula

I know I can put autofit on the cell however when only one line of text is returned it leaves 9 linbes of white space which is undesirable

Thank you

Troy Geri

 

[Jaafar Tribak]

You could use sheet calculate event as follows :

Private Sub Worksheet_Calculate()
    Range("d1").WrapText = True
    Range("d1").EntireRow.AutoFit
End Sub

where F1 is the cell with the VLookUp function. Change the cell as needed.

 

[troygeri]

Thanks - works nicely