Auto-adjust row height to fit text

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
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.)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DayTimeSeby

New Member
Joined
May 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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....

VBA Code:
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

VBA Code:
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.
 

Forum statistics

Threads
1,141,816
Messages
5,708,753
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top