Adjusting to a Maximum Row Height

by Allen Wyatt
(last updated July 4, 2015)

13

Paul has a worksheet that has over a thousand rows of data in it. Most of the rows have a height of 12, but some have a height of over 100. He wonders if there is a way to adjust the row heights so that no row is over 40? (Meaning, those under a height of 40 will retain their current height, but those over 40 will be adjusted to 40.)

Tackling this issue is actually quite easy—you just need a way to step through the rows, determine the current row height, and then adjust the row height if it is too large. This can be done with a very simple macro, such as the following:

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

The macro determines the range of used rows in the worksheet and then steps through those rows. The operative property is the .Height property, which is checked. If it is over 40, then it is set to 40. This means that anything with a row height of less than 40 is left unchanged.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10381) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Controlling Automatic Indenting

Type a tab character and you might just find that Word adjusts the indentation of the entire paragraph. If you don't like ...

Discover More

Tying Workbooks Together

If you work with multiple workbooks at the same time, you might wonder how to tie them together so they open and close and ...

Discover More

Summing Every Fourth Cell in a Row

Need to sum a series of cells that fits some regular pattern? Here are several ways that you can get the summation that you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Changing Default Row Height

Changing the default row height used for a worksheet is relatively easy, as long as you don't mind the row height never ...

Discover More

Hiding a Huge Number of Rows

Need to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques you ...

Discover More

Automatic Row Height For Merged Cells with Text Wrap

When you have text wrap turned on in a cell, Excel expands the height of the row as you add more text to the cell. When you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2017-10-13 13:44:23

John Doe

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


2017-10-13 12:18:07

John Doe

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


2016-11-29 13:42:10

paul tonello

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


2015-07-14 06:33:58

balthamossa2b

@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.


2015-07-13 10:09:51

Paul

Thanks to all for the helpful comments!
-Paul


2015-07-11 11:10:34

Geoff Hummel

@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.


2015-07-06 19:29:41

Gavona

>>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.


2015-07-06 08:32:06

Brian Walker



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
...


2015-07-05 05:21:40

balthamossa2b

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).


2015-07-04 20:51:08

Geoff hummel

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.


2015-07-04 17:04:10

Locke Garmin

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


2015-07-04 05:35:13

Tousif Chamanshaikh

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??


2015-07-04 05:33:06

balthamossa2b

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.