Automatically Adjusting Row Height to Show Multiple Lines of Text

I have a group of cells merged into a single row and have it formatted to wrap the text. Is there a way for the row height to automatically adjust to show all of the text when the text string is longer and wraps into two or more lines?

Rigth now the only way I can see the multiple rows of text is to manually stretch out the row height.

Thanks!

Ace_XL,

Yes, I really need tomerge the cells. This is part of a form and we need the text to go all the way across the page and then wrap down to a second (or even third) line when necessary. Like it would on a Word document.

So are you saying that it can't be done when the cells are merged?

Madmanmac,

In my experience I have not been able to get this to work either. As a workaround my colleagues and I just make the cell as large as we think it needs to be to capture the input.

Here's a simple spreadsheet to play with which illustrates the point...perhaps someone else can play along with it and find a solution.

Sorry!

make the cell as large as you need. Then make activ ( blue) all the rows that contain the cells that you would like to autoadjust. Double klik on any row border when the cursor changes from arrow to the other sign.Fullscreen capture 23.01.2013 201037.jpg

Select the Developer Tab, click on Visual Basic select the sheet you want to contain the autofit. When the sheet window pops up select "change" from the drop down menu on the top right and paste the following autofit command into the sheet:


Rows("9:100").EntireRow.AutoFit


It should be in between

Private Sub Worksheet_Change(ByVal Target As Range)

AND

End Sub

Make sure to capture the rows you want to autofit, you are telling the sheet to autofit the rows everytime a change occurs. This is my first attempt to us VB and it seems to work fine. Please let me know if this works for you.

I am not at all familiar with Visual Basic but I did what you said and I think I did it correctly. But it did not work for me.

Any ideas?

Originally Posted by JDUBS1080

Select the Developer Tab, click on Visual Basic select the sheet you want to contain the autofit. When the sheet window pops up select "change" from the drop down menu on the top right and paste the following autofit command into the sheet:


Rows("9:100").EntireRow.AutoFit


It should be in between

Private Sub Worksheet_Change(ByVal Target As Range)

AND

End Sub

Make sure to capture the rows you want to autofit, you are telling the sheet to autofit the rows everytime a change occurs. This is my first attempt to us VB and it seems to work fine. Please let me know if this works for you.

So we are clear this is blind leading blind so forgive me if I'm not that helpful, here's a picture of my screen with the text entered.

VB Row_Sizer.jpg


If thats not helpful here's the long version of how I got this to work for me.

I started by first recording a macro I called row_sizer. Click record macro select the rows to autofit and double click in between rows, then stop recording. I also gave it a keyboard shortcut of Ctrl+s. Your workbook needs to be save as a macro-enabled workbook as well.


Then go into the macro and trim down the scrolling commands and select commands until you just have something like this:
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Row_Sizer Macro
' Adjust Row Height based on text
'
' Keyboard Shortcut: Ctrl+s
'
Rows("9:100").EntireRow.AutoFit

End Sub

Select everything between private sub... and end sub including the spaces and copy and paste into the worksheet change area I previously suggested. It works for me with all of the spaces and text in there and also with only the Rows("9:100").EntireRow.AutoFit

Please let me know if either works for you.

Originally Posted by JDUBS1080

So we are clear this is blind leading blind so forgive me if I'm not that helpful, here's a picture of my screen with the text entered.

Attachment 209214


If thats not helpful here's the long version of how I got this to work for me.

I started by first recording a macro I called row_sizer. Click record macro select the rows to autofit and double click in between rows, then stop recording. I also gave it a keyboard shortcut of Ctrl+s. Your workbook needs to be save as a macro-enabled workbook as well.


Then go into the macro and trim down the scrolling commands and select commands until you just have something like this:
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Row_Sizer Macro
' Adjust Row Height based on text
'
' Keyboard Shortcut: Ctrl+s
'
Rows("9:100").EntireRow.AutoFit

End Sub

Select everything between private sub... and end sub including the spaces and copy and paste into the worksheet change area I previously suggested. It works for me with all of the spaces and text in there and also with only the Rows("9:100").EntireRow.AutoFit

Please let me know if either works for you.

Still didn't get it to work. Perharps you could attach a simple spreadsheet for me to look at? Thanks.