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, and 2013.
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 MoreWant to set the width and height of a row and column by specifying a number of inches? It's not quite as straightforward ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-10-13 13:44:23
John Doe
It's faster and not as jumpy to collect all the rows into a range and set the row height once instead of for each row individually.
Sub MaxRowHeight()
Dim R As Long
Dim tallRows As Range
For R = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(R).Height > 40 Then
If tallRows Is Nothing Then Set tallRows = Rows(R) Else Set tallRows = Union(tallRows, Rows(R))
End If
Next
If Not tallRows Is Nothing then tallRows.RowHeight = 40
End Sub
2017-10-13 12:18:07
John Doe
It's faster and not as jumpy to collect all the rows into a range and set the row height once instead of for each row individually.
Sub MaxRowHeight()
Dim R As Long
Dim tallRows As Range
For R = 1 To ActiveSheet.UsedRange.Rows.Count
If Rows(R).Height > 40 Then
If tallRows Is Nothing Then Set tallRows = Rows(R) Else Set tallRows = Union(tallRows, Rows(R))
End If
Next
tallRows.RowHeight = 40
End Sub
2016-11-29 13:42:10
I read your suggestion for row height:
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
Question; where would I insert it and are there caveats to its' insertion. I have one userform which has a save button. When I save and revert back to my spread sheet; I keep finding myself having to use Clear > Clear Formats to reduce the row height back to its intended size. (I have developed a macro ctrl -h to do this.)
Thanks
2015-07-14 06:33:58
@Geoff
Don't think so, pixel measures in Excel is a mess. The value set in a macro is not the same as the one set by right-clicking in the sheet, for some magical and weird reason.
The best solution I see is to manually line break EACH line. Then count the line breaks through a macro (I think they count as a character?) and multiply by the height of a single line.
But yeah, the hard part would be to take your existing data and reformat it with manual line breaks.
2015-07-13 10:09:51
Paul
Thanks to all for the helpful comments!
-Paul
2015-07-11 11:10:34
Geoff Hummel
@balthamossa2b
I use line breaks (alt+Enter) frequently in text fields (helps readability).
So need a way to calculate the height (in pixels) of the text and the number of lines of text based upon the column width. Can then use the calculated pixel height to set the row height.
2015-07-06 19:29:41
Gavona
>>Geoff Hummel:
If you do not set an explicit row height (in other words Format, Autofit Row Height) you will find that the height does indeed adjust to fit the data provided:
i) you have set the cell to Wrap Text
ii) unless the cell contains line jumps (Alt + Enter).
iii) With older versions of excel at least wrapping would not be reliable if you have more than 255 characters in the cell.
2015-07-06 08:32:06
Brian Walker
I usually find the last row and use it as the endpoint.
Dim Re As Long
Re = ActiveCell.SpecialCells(xlLastCell).Row
For i = 1 to Re
If Rows(i).Height > 40 Then Rows(i).RowHeight = 40
Next i
...
2015-07-05 05:21:40
I just tested the macro, and as I thought it skips hidden rows (since you can't select them with a mouse AFAIK).
@Geoff
If you select Wrap Text in cell format this will work, unless the cell contains line jumps (Alt + Enter).
2015-07-04 20:51:08
Geoff hummel
What I'd like to see is a way to adjust the row hight based upon the text in a cell. So all the text wrapped to fit the column width is displayed by adjusting the row hight.
2015-07-04 17:04:10
Locke Garmin
For most use cases the code in this tip will work fine. The only issue is when you have a worksheet that you started your data in a row other than row 1. The 'Worksheet.UsedRange' Property won't automatically include the first rows of data if every cell in those rows are empty and never been touched. So if the Used Range starts at row 2 and ends at row 4, then this macro will go through rows 1 to 3 because the count always starts at 1 , and the number of rows from row 2 to row 4 is 3 rows.
I would suggest the following changes to fix the (potential) issue:
Sub MaxRowHeight()
Dim Row As Range
For Each Row In ActiveSheet.UsedRange.Rows
If Row.Height > 40 Then Row.RowHeight = 40
Next
End Sub
2015-07-04 05:35:13
Tousif Chamanshaikh
I am working on an industrial project in which i am dealing with multiple workbooks(21-excel files).Each file contains 8 worksheets.I want to club this all together.Is it possible??
2015-07-04 05:33:06
I didn't know RowHeight was a thing. I would have used Range.EntireRow.Height.
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 © 2019 Sharon Parq Associates, Inc.
Comments