In March I wrote a post on using a macro to apply column Autofit on a sheet but with a maximum column width. This is a follow up post as someone requested the same functionality for row height.
You can see the previous post here.
The code below works on the active sheet. I have replaced the variable c with r (for rows) and changed all the column commands and variables to rows.
The only problem with this technique is that the main reason a row height changes is to display a cell with a larger font. If you reduce the row height then the contents of those larger font rows may not display properly.
Sub AutoFitAllRowsActive() Dim r Dim ws As Worksheet Dim lnumRows As Long Dim lMaxHeight As Long Set ws = ActiveSheet lnumRows = ws.UsedRange.Rows.Count lMaxHeight = 40 ws.Rows.AutoFit For r = 1 To lnumRows If ws.UsedRange.Rows(r).RowHeight > lMaxHeight Then ws.UsedRange.Rows(r).RowHeight = lMaxHeight End If Next r Set ws = Nothing End Sub