How to make Excel's "Auto Fit Row Height" feature actually auto fit the row height?

A method I just found (extending the previous post a little):

• Select the whole sheet.
• Resize a column a little wider (because the whole sheet is selected, all the columns will get wider)
• Double-click a row separator - the row height will be auto-fitted
• Double-click a column separator - the column widths will be auto-fitted

Voila!

Excel's WYSIWYG isn't the best. In your picture, 'cat.' just barely sneaks into the 5th line. If you reduce the zoom percentage to anything less than 100% (99% for example.) then 'cat.' is now wrapped down to the 6th line. I think Excel is trying to auto-fit in a way that will ensure everything is almost always visible no matter your zoom level.

That isn't the only problem you will have with AutoFit. In addition, the way a word-wrapped cell is printed won't always match what you see on screen. Take your example and change the font to Courier while leaving size at 11.

As you can see, cell A1 appears to be given 1.5 extra lines. Now look at print preview, 'cat.' is completely hidden.

In my experience, Excel has this problem with certain fonts and font sizes more than others. Courier is worse than Courier New, size 11 is generally worse than 10 or 12. (Why they picked size 11 as the new default, I have no idea.)

I just wrote a small macro for that purpose. I assigned it a shortcut key (Ctrl-Shift-J) and it works like a charm for me.

Sub ArrangeTable()
'set proper column width and row height for the current region
    With Selection.CurrentRegion
        .Columns.ColumnWidth = 200
        .Columns.EntireColumn.AutoFit
        .Rows.EntireRow.AutoFit
    End With
End Sub

To make this permanently available, you can easily make a automatically loading add-in:

• create a module in a fresh blank workbook and paste the code,
• assign it the shortcut key you want,
• then Save As, Add-in, to folder %appdata%\Microsoft\Excel\XLSTART
  This way it will be invisibly loaded every time you start Excel.

In some cases, the problem of extra space in some rows after invoking "AutoFit Row Height" may be that a hidden column has wrapped text in those rows.