Adjusting to a Maximum Row Height

by Allen Wyatt
(last updated July 4, 2015)


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


Making Word Stop at the End of a Document when Reviewing Changes

The Track Changes tool is a great help when you want to see what edits have been made to a document. When you review those ...

Discover More

Changing How Word Flags Compound Words

It is not uncommon to add hyphens between words to help clarify the meaning of your prose. You might even add non-breaking ...

Discover More

Understanding Outlining

Outlining, a feature built into Excel, can be a great way to help organize large amounts of data. This tip provides an ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Adjusting Row Height for Your Text

Want Excel to automatically adjust the height of a worksheet row when it wraps text within the cell? It's easy to do, relying ...

Discover More

Automatic Row Height for Wrapped Text

When you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel doesn't ...

Discover More

Formatting Subtotal Rows

Excel automatically formats subtotals for you. But what if you want to change the default to something more suitable for your ...

Discover More

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

View most recent newsletter.


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

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


2015-07-14 06:33:58



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


Thanks to all for the helpful comments!

2015-07-11 11:10:34

Geoff Hummel


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


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


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


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

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


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

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.