Excel – wordwrap row autosize issue

I came upon an Excel annoyance last week. I have hit upon it before, but this week, I needed to find a solution.

What’s the problem?
The annoyance is this: merged cells that contain a lot of text do not automatically see their row size grow to fit the amount of test.

The solution is manual: you can resize the row manually, however you cannot double click on the row dividers and let Excel increase the row size to suit the amount of text in the merged cells.

Here’s a little more background to the problem. The screenshot below demonstrates that we can have a single (non-merged) cell word wrap correctly. However it does mean that the entire width of column A is affected, thus the date is pushed to the right.

excel1

The first solution that we might try is to merge a few cells. This works, date is not pushed to the right. However, Excel’s ability to increase the row size to suit the amount of text is lost – double clicking on the row divider does not work and simply results in a single line of visible text, as shown below:

excel4

In order to alleviate this problem, we have to resize the row manually. This is a tedious process and one that does not lend itself to automation, i.e. through programmatic means. Nonetheless, here’s a screenshot of the resized row:

excel2

The solution
The real solution, and it feels like a fudge, but it works very well, is this: use merged cells as before, somewhere to the right of the cell with merged text (e.g. A1), create a copy of the cell’s content (if your text is in cell A1, make cell Z1 “=A1”). Resize the copied cell (Z1) width to match the width (and formatting) of the merged cells (A1). Excel’s row auto-size feature will now work manually and programmatically. Here a screenshot that might help:

excel3

Perhaps this is best explained by use of an example spreadsheet.

Now, the reason why I making such a big thing about this is simple: Excel is great for producing tabular reports. However, to get some formating to work “just right”, it pays to using Excel automation, i.e. work with the format styles programatically. It is for this reason that I find myself writing the following snippet of code:


[C#]
const int cA = 1;
excelWorksheet.Cells[7, cA] = [long string of text];
Excel.Range r = excelWorksheet.get_Range("B7", "B7");
r.EntireRow.AutoFit();

It is the last two lines of code that perform the same function as double clicking on the row divider, thus the row grows to the correct height (albeit of the copied cell, Z1).

45 thoughts on “Excel – wordwrap row autosize issue”

  1. I can sympathize with your, as I’ve come across this problem many times. It doesn’t seem like a big issue, but over time it becomes very annoying. Thanks for the tip you left….I’ll use it until MS finally decides to look at the problem.

    Cheers,

    Trevor

  2. Thanks a lot for the tips.
    BTW. Does the problem still exist in MS Excel 2003?

  3. Great trick, but I have found one other issue with this. If you later delete the contents of the merged cell, and then hit Delete one more time, the row reverts back to its original height (no other merged cells in the row). That’s good. If you then go back and enter new info into the merged cell, the row will no longer autofit. Somehow, the “delete” resets the autofit for that row.

  4. Thanks for this tip. It was very helpful.

    I have a related issue that I wonder if you have run into and/or have any suggestions about. On a spreadsheet with merged cells, the screen display shows two lines and the row height is adjusted accordingly. If the spreadsheet is printed to an Adobe PDF print driver, however, the text fits onto one line. In the pdf, the row height remains as if there are two lines of text, leaving a blank row below the text.

    I have Googled this issue a number of different ways but can’t find anything on this. Any help/ideas/suggestions would be appreciated.

    Thanks.

  5. A simpler way of doing this:
    1. Open the Visual Basic editor.
    2. Double-click the sheet you are using (displayed on the left) and notice a “code window” appears in the middle of the screen.
    3. In the left dropdown of the code window (General), choose “Workbook”.
    4. In the right dropdown, choose “Selection Change”.
    5. Insert the following lines of code between “Private Sub” and “End Sub”, replacing the number after “Sheet” with the corresponding number of your sheet.

    Sheet4.Columns.AutoFit
    Sheet4.Rows.AutoFit

    This will autofit your rows and columns each time you make a change to the worksheet. It is automatic, and you will not have to bother with it again for that sheet.

    Beware: If you don’t have your cells formatted for text wrap, the autosize will make the cell VERY wide!

  6. I’ve found that the solution to this problem is to copy the contents of the problem cell and “Paste Values” (Edit>Paste Special>Values) into the exact same cell. You could incorporate this into a macro if you prefer.

  7. Without merged cells, some files will autosize a row height with a cell wrapping, some files won’t – I cannot figure it ??

  8. I had a problem similar to the one stated above, except the cell would only display “#” characters in the merged cell until you double click on it. Then it would display the entire paragraph properly, but clicking outside the cell would return it to the original view.
    Confusing, no?

    I determined it’s either an issue with Office 2007 or with merging cells. Unmerging then remerging the cell and pasting the data back in should fix it.

    Your site was the most helpful of all the ones I tried.
    Kudos.

    NuclearPeon

  9. I think this is a problem in 2003 and 2007. Is the column formatted as Text? If so then if the text is longer than 255 characters you get a line of hashes and no notice is taken of the row height or autofit. Format to general and the problem disappears. This seems to be hard coded into Excel as nothing I have done will change the behaviour.

  10. Step 6 above by MICMC solved my problem with auto row height. MICMC, thanks for your exact instructions on how to use VB. I tried it with the Sheet4.Columns.AutoFit but that messed up my columns, so I removed it. The second statement Sheet4.Rows.AutoFit, however, solved all my row height problems. Thank you very much.

  11. Thanks for the tip. So annoying that you have to use the work around.

  12. I found that the vertical alignment of the cells must be set to “Top” in order for this to work. I’m using Excel 2003.

  13. Have not tried this on versions before 2007, but it should work:

    AutoFit actually works on cells that have Center Across Selection as their alignment

    (Assuming Cell is A1 and the text spans 5 columns)
    With Cells(1, 1)
    .Formula = txtTitle
    Range(.Cells,.Offset(0, 4)).HorizontalAlignment = 7 ‘ xlCenterAcrossSelection
    .WrapText = True
    .EntireRow.AutoFit
    End With

    *typing from memory, vet through before copying*

  14. Following on from what STARFIRE mentioned, below is some code I used to automatically unmerge / remerge some cells and set the height correctly. You’ll need to use this in the Worksheet>Change event sub:

    …one of the problems with using this event is that Target refers to the changed cell, whilst the ‘ActiveCell’ or ‘Selection’ could be the range below it (if the user used the return key to enter the text). To get around this I had to use the ‘rng’ object to save where the user’s selection was, so I could reselect it later.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim r As Single

    Application.ScreenUpdating = False
    Set rng = Selection
    Target.Select
    Selection.MergeCells = False
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlCenterAcrossSelection
    r = ActiveSheet.Rows(Selection.Row).RowHeight
    Selection.MergeCells = True
    Selection.HorizontalAlignment = xlLeft
    ActiveSheet.Rows(Selection.Row).RowHeight = r
    rng.Activate
    Application.ScreenUpdating = True

    End Sub

    ***I copied this from my workbook directly, so it should just work***

  15. I’m having an issue with Excel 2003 where in the print preview or when looking at the spreadsheet on the screen everything looks ok but when I print words are printing out of their cells. My word wrap in merged cells doesn’t seem to be working.

    Any attempts to readjust the cells or formatting the cells has not worked.

    Any help would be appreciated.

  16. cg 5:15 pm on July 22nd, 2008 11
    similar to CG above, changing the format worked for me. Using “General” didn’t work for me, but inexplicably when I changed the format to “Time” or “Currency” it worked!

  17. Thanks MICMC! Your suggestion worked perfectly. Note to others, make sure that you are using the correct sheet number. MICMC’s code refers to Sheet4.Columns.AutoFit and if you are like me, Sheet1 was needed.

  18. Can you believe this STILL happens in Excel 2010? I can’t believe it’s a problem after all these years.

  19. Great solution – set me well on the right direction. Here are the steps I did to fix the issue

    1. Recorded the column width of the random cell to populate and autofit
    3. Re-size the random cell to the size of the merged cells
    4. Copy the value of the merged cells into the random cell
    5. Set the WrapText property to true on the random cell
    6. Initiate AutoFit() on random cell
    7. Record the new row height of the merged cells
    8. Delete the value from the random cell (which causes the merged cells to resize back to normal)
    9. Re-set the row height of the merged cells to the variable recorded in step 7.
    10. Re-set the column width of the random cell to the variable recorded in step 1

    This should make everything like like it did before as well as give you the correct row height on your merged cells.

  20. Great solution MICMC and works well almost all the time; but there still remains one problem, and it’s shown in your example above. I’ve gotten this to work and loop via macro, and plan to use it hundrads of times for my job, but you’ll notice that wrapped text in merged cells is treated differently than wrapped text in non merged cells which have an equal width to the merged. In your example, under ‘Title’, see how the last line in the merged cell reads ‘divider) and programmatically’; while your unmerged cell of equal width and same content has the last line of just ‘programmatically’. Excel, for some reason, is wrapping the text differently, and I can not figure out why. This occasionally leads to a space equal to an extra line, or one line being cut off after auto fitting. Any solutions to this??

  21. Simple and wonderful solution. Wish it weren’t necessary but thanks! Rob

  22. Don’t know if this is everyone’s problem or not. Was having the issue of the cells with wrap text not showing all the text. If we selected the whole column and then used the Home>Cels>Format>AutoFitRowHeight or double click the bottom of the row to auto adjust… some cells would be hiding some text. Our issue was simply the zoom level. Once we zoomed to 100% everything was fine.

  23. Andrew (August 20th, 2010) – you’ll notice that the reason behind the wrong mapping in the example post is because column J (in the example) is slightly bigger.

    One thing I have noticed is that excel columns have 2 different sizes, which you’ll notice when you hold down your mouse on one of the grippers (3.57 (30 pixels)).

    Now – the standard column width is 8.43 (64 pixels). Now try and expand the column to 128 pixels (double the size). You would EXPECT 16.86 (128 pixels), but you ACTUALLY get 17.57 (128 pixels). I imagine that is the problem you are encountering.

    If you merge 2 cells, I expect that excel treats the first size value as gospel, whereas you’re probably expanding based on pixels.

  24. Let’s say you have 2 merged cells as my example (post 28). I assume what you have done is expanded a cell to the correct size in PIXELS, and set the size in your macro to 17.57, when actually you probably need to set it to 16.86

  25. Thanks for the good tip; I see it’s a bit old, but old tricks still work…

  26. This works just fine thanx for the solution to a very annoying problem.

    42

  27. MicMC’s solution works fine except the macro is run everytime you change one cell making it look very jumpy.

    Is there a bit of additonal code that says every time the selection is changed in one column only (the one width the long text), the Macro is run?

  28. Why of why is MS so arrogant that they ignore a small but extremely annoying problem that has been discussed by so many on multiple internet sites for 5+ years? Shame!

  29. I agree Jorobli!

    I’ve been having this problem in documents I’m creating at work. I always thought there was some weird formatting option selected, but it’s good to know it’s a bug. I was searching for 20 minutes for a fix in excel before I used google.

  30. Here’s a Refinement of MICMC’s solution. If you use the worksheet selection change event and the me keyword instead of the workbook event then it solves the issue for that worksheet even if the worksheet name is changed at some time.

    1. Open the Visual Basic editor.
    2. Double-click the sheet you are using (displayed on the left) and notice a “code window” appears in the middle of the screen.
    3. In the left dropdown of the code window (General), choose “Worksheet”.
    4. In the right dropdown, choose “Selection Change”.
    5. You will want have the following code for the worksheet selection change event:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Rows.AutoFit
    End Sub
    6. You don’t need to autofit the columns to solve this problem and you may not want the column widths to change automatically. If you do want column autofit as well then modify with added line as shown below.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Rows.AutoFit
    Me.Columns.AutoFit
    End Sub

  31. Reading furthur I saw STEVEB’s good comment. There is no need to autofit every time the cursor moves.Better to use the Worsheet_Change event instead of the Worksheet_SelectionChange. This makes the autofit code only run when the contents of a cell is changed and it will solve the jumpy problem.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Rows.AutoFit
    End Sub

  32. Kea’s solution works fine but do not forget to set the vertical alignment to ‘top’. Anyhow, only then it worked like a charm for me.
    I also noticed the extra line coming up like Andrew mentioned on 20aug2010.
    And indeed when you then delete the single adjusted width-column holding the string then all pops back to it’s original non-satisfying state.
    Since I want this behavior all the time, is there a way to incorporate this in a “class” or so that it is always loaded with new sheets?
    Or create a button for it.
    Or will M$ finally fix this? Anyhow I submitted this link to them. The issue seems already to be noticed to them since Excel 97!

  33. changing the font has solved many auto-resize issues for me in excel. in Calibri auto-resize does not work, in Arial it does.

    hope this helps somebody.

  34. I done this very easily by taking my target cell at the width i need it at and the typing in about 10 or 20 lines of any text filling the cell. Then i copied this to all the cells below . which all showed all cells with text filling it to the height it was at. Then i went to the side and double clicked to autosize the rows and they all expanded . i finally highlighted all the cell and right click clear contents . all the contents cleared and the rows go back to original height. the cells think they are 10 or 20 lines deep and when you type in them and move onto another cell they auto size every time . Cheers

Comments are closed.