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 Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want Excel to automatically adjust the height of a worksheet row when it wraps text within the cell? It's easy to do, ...
Discover MoreAdjusting the height of a row or range of rows is relatively easy in Excel. How do you adjust the height of those same ...
Discover MoreWhen you format a cell so that the information within it can wrap to multiple lines, you may be surprised if Excel ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2021 Sharon Parq Associates, Inc.
Comments