Written by Allen Wyatt (last updated January 13, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10381) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
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!
When you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...
Discover MoreIf you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize ...
Discover MoreNeed to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-09-15 17:25:40
Rama
This is a great tip! To show at most three lines set RowHeight=45, four lines RowHeight=60 and five lines RowHeight=75
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments