Jeaux works with a lot of long worksheets that contain data she may need to print. Once printed, she then writes in the space next to the column of data. All rows are auto sized to fit contents, therefore they vary in height. Jeaux is looking for a macro that will take the selected rows and incrementally increase them, say by a given percentage. (Since they are different heights, she cannot just set them all to the same height.) This way, she can make the rows large enough for handwriting, but still be able to see all of the data. It would also be nice to have a way to set the rows' height back to what they were before she printed.
This task is very easy to accomplish with a macro. All you need to do is step through the selected rows and adjust the RowHeight property, as is done in this macro:
Sub ExpandSelectedRows() Dim rRow As Range Dim dEnlarge As Double dEnlarge = 1.25 For Each rRow In Selection.Rows rRow.RowHeight = rRow.RowHeight * dEnlarge Next End Sub
In this case, the dEnlarge variable contains 1.25, which means that the formula in the For ... Next loop will increase the row height by 25 percent. The value of this variable can be changed to reflect the percentage you want to use, or you could modify the macro to ask the user for a percentage:
Sub ExpandSelectedRows() Dim rRow As Range Dim dEnlarge As Double Dim sTemp As String sTemp = InputBox("Increase by what percent?") dEnlarge = Val(sTemp) If dEnlarge > 1 Then dEnlarge = dEnlarge / 100 If dEnlarge < 1 Then dEnlarge = dEnlarge + 1 If dEnlarge > 0 Then For Each rRow In Selection.Rows rRow.RowHeight = rRow.RowHeight * dEnlarge Next End If End Sub
If you later want to get the rows back to their original height, the following single-line macro will do the job:
Sub AutfitRows() Cells.EntireRow.AutoFit End Sub
This macro works because the rows in your worksheet originally were "auto sized to fit contents." While a macro could have been written to reverse the enlarging steps (dividing by dEnlarge instead of multiplying), it would only work reliably if the ExpandSelectedRows macro wasn't run multiple times.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (21) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize ...
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 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-03-20 14:17:53
Peter Johnson
Excel has an absolute maximum RowHeight of 409.5r
r
The statement below will fail if rRow.RowHeight * dEnlarge > 409.5 (you get a run-time error ‘1004’ Unable to set the RowHeight property of the Range class.)r
r
rRow.RowHeight = rRow.RowHeight * dEnlarge r
r
To have a robust macro you need to replace the above line by:r
r
If rRow.RowHeight * dEnlarge < 409.5 Thenr
rRow.RowHeight = rRow.RowHeight * dEnlarger
Elser
rRow.RowHeight = 409.5r
End Ifr
(see Figure 1 below)
Figure 1. Error if row too high
2017-03-18 08:11:23
Alan Cannon
The second macro above, the one with the message box input, will actually shrink the rows to the input divided by 100. To increase the height by the input value, entered as a whole number such as 25 (for 25% increase) you should multiply the row height by 1 + dEnlarge/100.
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