Auto-adjust row height to fit text

[mrblister]

Sometimes I would like one or more cells to always display all the text in the cell(s), and for Excel to auto-adjust the row height properly if I adjust the size of the column the cell (or cells) is in.

But I don't know how to get the above. For example, if I want to display all the text in the cell, I select "Wrap Text". Now if I shrink the size of the column, the row height automatically adjusts bigger. But an issue occurs when I enlarge the size of the column; the row height won't "shrink" to adjust to the extra space in the cell, leaving blank gaps at the top of the cell. To fix this, I click at the end of the text in the cell and press enter, and this will fix this specific cell (I need to do this for all other cells). Is there a way to set Excel to auto-adjust row height bigger AND smaller? I'm sure VBA solutions exist, but shouldn't/doesn't Excel do this natively? (Note I also tried using "AutoFit Row Height" but this seems to not work either, as shrinking the column size will no longer auto-enlarge the row height.)

 

[DayTimeSeby]

Try either one....

I just recorded these with the built-in macro recorder editing out any unnecessary actions. I had a decent data set where I messed up a portion of columns and rows to make it aesthetically unpleasant/ possibly triggering to those that may be compulsive /obsessive. Highlighted the whole sheet by clicking the top left corner, "highlight the whole sheet spot". Right after that, I double-clicked between cells both columns...and rows...

As far as what you are looking for that I got out of the request, this does the trick. I'd suggest setting some basic formatting prior though if you want certain cells to wrap text etc....

Sub resetdimmensh()
'
' resetdimmensh Macro
' resets the row and columns sizing
'

'
    Cells.Select
    Range("A1").Activate
    Cells.EntireColumn.AutoFit
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Cells.EntireRow.AutoFit
    Range("A1").Select
End Sub

Sub resizerDimmy()
'
' resizerDimmy Macro
' DIMMENTIAAA
'

'
    Range("A1").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Range("A1").Select
End Sub

I paid attention as to not have anything reference specific workbook or work sheet- therefore these will be able to work on which ever document you throw these at.

I myself am going to keep these snips of VBA for a one of those lazy days. Very possibly add it onto a printing macro to format the print job up all nice an purdy.

 

[mrblister]

Try either one....

Haven't had the chance to try these yet, but thanks for these!

Cheers!