Adjusting to a Maximum Row Height

It's faster and not as jumpy to collect all the rows into a range and set the row height once instead of for each row individually.


Sub MaxRowHeight()
Dim R As Long
Dim tallRows As Range
For R = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(R).Height > 40 Then
If tallRows Is Nothing Then Set tallRows = Rows(R) Else Set tallRows = Union(tallRows, Rows(R))
End If
Next
If Not tallRows Is Nothing then tallRows.RowHeight = 40
End Sub

It's faster and not as jumpy to collect all the rows into a range and set the row height once instead of for each row individually.


Sub MaxRowHeight()
Dim R As Long
Dim tallRows As Range
For R = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(R).Height > 40 Then
If tallRows Is Nothing Then Set tallRows = Rows(R) Else Set tallRows = Union(tallRows, Rows(R))
End If
Next
tallRows.RowHeight = 40
End Sub

I read your suggestion for row height:
Sub MaxRowHeight()
Dim R As Long

For R = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(R).Height > 40 Then Rows(R).RowHeight = 40
Next
End Sub

Question; where would I insert it and are there caveats to its' insertion. I have one userform which has a save button. When I save and revert back to my spread sheet; I keep finding myself having to use Clear > Clear Formats to reduce the row height back to its intended size. (I have developed a macro ctrl -h to do this.)

Thanks

@Geoff

Don't think so, pixel measures in Excel is a mess. The value set in a macro is not the same as the one set by right-clicking in the sheet, for some magical and weird reason.

The best solution I see is to manually line break EACH line. Then count the line breaks through a macro (I think they count as a character?) and multiply by the height of a single line.

But yeah, the hard part would be to take your existing data and reformat it with manual line breaks.

Thanks to all for the helpful comments!
-Paul

@balthamossa2b

I use line breaks (alt+Enter) frequently in text fields (helps readability).

So need a way to calculate the height (in pixels) of the text and the number of lines of text based upon the column width. Can then use the calculated pixel height to set the row height.

>>Geoff Hummel:
If you do not set an explicit row height (in other words Format, Autofit Row Height) you will find that the height does indeed adjust to fit the data provided:
i) you have set the cell to Wrap Text
ii) unless the cell contains line jumps (Alt + Enter).
iii) With older versions of excel at least wrapping would not be reliable if you have more than 255 characters in the cell.

I usually find the last row and use it as the endpoint.

Dim Re As Long

Re = ActiveCell.SpecialCells(xlLastCell).Row

For i = 1 to Re
If Rows(i).Height > 40 Then Rows(i).RowHeight = 40
Next i
...

I just tested the macro, and as I thought it skips hidden rows (since you can't select them with a mouse AFAIK).

@Geoff

If you select Wrap Text in cell format this will work, unless the cell contains line jumps (Alt + Enter).

What I'd like to see is a way to adjust the row hight based upon the text in a cell. So all the text wrapped to fit the column width is displayed by adjusting the row hight.

For most use cases the code in this tip will work fine. The only issue is when you have a worksheet that you started your data in a row other than row 1. The 'Worksheet.UsedRange' Property won't automatically include the first rows of data if every cell in those rows are empty and never been touched. So if the Used Range starts at row 2 and ends at row 4, then this macro will go through rows 1 to 3 because the count always starts at 1 , and the number of rows from row 2 to row 4 is 3 rows.

I would suggest the following changes to fix the (potential) issue:

Sub MaxRowHeight()

Dim Row As Range

For Each Row In ActiveSheet.UsedRange.Rows
If Row.Height > 40 Then Row.RowHeight = 40
Next

End Sub

I am working on an industrial project in which i am dealing with multiple workbooks(21-excel files).Each file contains 8 worksheets.I want to club this all together.Is it possible??

I didn't know RowHeight was a thing. I would have used Range.EntireRow.Height.