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.
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 building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to ...
Discover MoreWhen you enter information into a row on a worksheet, Excel automatically adjusts the height of the row based on what you ...
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 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