Written by Allen Wyatt (last updated January 13, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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 2021.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Need to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques ...
Discover MoreWhen you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...
Discover MoreWant Excel to automatically adjust the height of a worksheet row when it wraps text within the cell? It's easy to do, ...
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