Written by Allen Wyatt (last updated April 15, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, 2016, 2019, Excel in Microsoft 365, and 2021.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Changing the default row height used for a worksheet is relatively easy, as long as you don't mind the row height never ...
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 MoreIf you have some cells merged in a worksheet, and you wrap text within that merged cell, Excel won't automatically resize ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-04-15 12:29:33
J. Woolley
My Excel Toolbox includes the AdjustRowHeights macro to adjust the height of selected rows by a fixed increment or a proportional multiplier. The AdjustColumnWidths macro will adjust the width of selected columns in the same way. Both macros support Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox/
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 © 2024 Sharon Parq Associates, Inc.
Comments