Resize Row Height - problems with wrapped cells

Location: http://www.mvps.org/dmcritchie/excel/resize.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
When you manually set a row height Excel treats that as an override to its automatic row height and respects it. What you could do is give an out of the way cell like in column IV a font size of say 16 so the default height of the row will be about what you want and yet it will enlarge as needed. -- Jim Rech

Resize Rows

Resize Manually

Once you resize a row, you are basically committed to resizing such rows from that point on.

To manually resize all rows, select all cell (Ctrl+A) then double-click on the any boundary between the column numbers. which is the same as Format, rows, resize ...

Resize Rows with an Event Macro

You can solve your problem by resizing all rows after a manual change to any cell. If the problem content is due to a formula it would get corrected on the next manual cell entry.

Install the following by right-click of the worksheet tab, then view code, and place the code inside. You now have a macro. If you did not have macros before you will now. You should make sure that your security level is set to medium. Tools, Macro, Security, [x] Medium 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False   'should be part of Change macro
    Cells.EntireRow.AutoFit
    Application.EnableEvents = True   'should be part of Change macro
End Sub
Warning when you turn off EnableEvents and the macro fails after turning them off you will have to run a regular macro to them back on.

More on event macros in http://www.mvps.org/dmcritchie/excel/event.htm

Resize when Merged Cells are involved

Merged cells will allow "wrap text" from Format>Cells>Alignment but Excel has a problem auto-fitting merged cells.  Jim Rech has a macro to unmerge them remerge cells to adjust row height. 

The macro will increase row height, but because another cell may need the height it will never reduce the row height, see AutoFitMergedCellRowHeight macro and a revision was written by Greg Wilson which he indicated is essentially the same as Jim Rech's code but is invoked on a selection change.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on March 31, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved