Autofit row height - depending on changing cell contents whose source come from lookup

[Hayley Howells]

Hi, thanks for reading - sorry for wordy title! I have a set of raw data on different sheets that is pulled into an excel table. I have a list of doctors, the items they prescribe are pulled into the table by using their unique code, and the indirect function. Against those items the are prescribed by the doctor, I want to annotate notes from a pre-defined list. So I have used a lookup to pull that information in from a separate list. But the notes against each drug prescribed vary from 1-10 lines. What I want is for when the list is updated (by choosing a different doctor from a drop down list, therefore updating to show the respective items they prescribed), for the cells to automatically resize based on the notes text that is pulled in from the lookup.
I already have wrap text and autofit height turned on, but the size of the cells remains the same, as if to fit the text that was present when autofit was turned on.

Any ideas?

 

[XLfriend777]

Hi Haley_Howells - Welcome to the forum! You might try something simple like the code below which selects the entire sheet and then AutoFits the columns and then the rows. Hope this helps get things started.

Sub AutoFit()
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
End Sub

 

[Hayley Howells]

Thank you very much Goesr! I hadnt thought of doing it that way - but using a button - will create a nice user friendly way to do it! Brilliant - cheers!

 

[XLfriend777]

Hi Hayley - I'm glad you liked the idea. You will find this forum full of friendly, useful advise and some really creative solutions to excel problems. Again - welcome to the forum and before you know it you will be answering questions!