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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize ...
Discover MoreWhen 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 ...
Discover MoreWhen building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to ...
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