Written by Allen Wyatt (last updated June 14, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Ron knows how to adjust the height of a group of adjacent rows. What he doesn't know (and needs to) is how to make row heights the same across several worksheets in the same workbook.
The trick to this operation is to simply make sure that you select all the worksheets you want to affect. Take a look at the worksheet tabs at the bottom of the program window. You should see one for each worksheet in your workbook. If you want to affect the rows in a series of consecutive worksheets, click the tab for the first worksheet in the series and hold down the Shift key as you click the tab for the last. If the worksheets you want to affect are not consecutive, click the tab for one of the worksheets and then hold down the Ctrl key as you click on the tabs for each of the others.
With all the worksheets you want to affect selected, select the rows within the worksheet you can see. As you adjust the row height for those rows, Excel automatically adjusts the row height for the same rows in each of the other selected worksheets.
When you are done, click on a single worksheet tab. This cancels the selected set of worksheets, and you can continue to work as you desire. (If you don't cancel the selection set, then any changes you make on the screen continue to be made in all the selected worksheets.)
If you need to adjust row heights quite a bit, and your formatting is always the same, then you might benefit from having a macro to affect the sheets. The following macro steps through each selected worksheet and adjusts the height of rows 1 through 5. (You should obviously change the row height in the macro and the row numbers to reflect what you really need.)
Sub row_hts() For Each wksht In Worksheets Set sht = wksht sht.Rows("1:5").RowHeight = 25 Next End Sub
You can easily assign the macro to a shortcut key or the Quick Access Toolbar so it can quickly be executed.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12515) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Adjusting Row Height for a Number of Worksheets.
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 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 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."
2022-06-14 10:39:01
Robert
Surendera, the line you ref to is code. It is the start of a 'For' loop and the line 'Next' ends the loop.
2022-06-14 10:36:21
JMJ
@Surendera M. Bhanot
No, there are no comments in this code: it's really code, that makes Excel to select successively every worksheet in the workbook.
This instruction matches the "next" just to lines under.
2022-06-14 04:08:02
Surendera M. Bhanot
Is tis line "For Each wksht In Worksheets" a comment or code??
2019-05-04 08:57:32
Willy Vanhaelen
I don't see the necessity of the line
Set sht = wksht
This macro works equally well:
Sub row_hts()
For Each wksht In Worksheets
wksht.Rows("1:5").RowHeight = 25
Next
End Sub
2019-05-02 05:31:51
George
Thanks Allen!
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 © 2023 Sharon Parq Associates, Inc.
Comments