# Let Excel set row height automated for x predetermined no. of rows per A4 sheet?

I'm a European user of MS Excel 2010 and I came up with the weird idea, that I want e.g. 31 rows evenly spread out over the full length of one sheet (here: A4), excluding the page margins of course.

Now, is there any way to do this in Excel? If not, how do I calculate the needed row height with a calculator? Or maybe create a template in Excel to calculate this for Excel?

I know I could fumble this second way together myself with a few manual calculations, but as I already wanted to ask this question about Excels ability, I hope you understand my lazyness. ;)

Assuming you are not adverse to using VBA in your spreadsheet, this is possible. First, if you do not already have the Developer tab on your Ribbon, go to File->Options, Customize Ribbon, and in the right-hand panel check on Developer then hit OK. Next, click the Developer tab, then Visual Basic. On the left-hand side, double-click This Workbook and paste the code below. Alter the Configuration as necessary. Now whenever you click to save your workbook, the rows will automatically adjust to the specified parameters.

It's worth noting the possibility of more than 31 rows fit on the page, despite the calculation otherwise; using Excel's Normal margins and header/footer, 34 rows fit on a page. Changing the margins and header/footer to 0, 31 rows fit on the page.

For that reason, I suggest double checking with Print Preview, and decreasing RowsPerPage accordingly if you do have more rows than expected per page. There is only so much resolution for actually setting Row Heights, so unfortunately it's not perfect. Hope it helps, anyway.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

' Configuration
RowsPerPage = 31            ' number of rows per page, as stated in question
NumberOfPages = 1           ' how many pages of data you have
NumberOfSheets = 1          ' How many spreadsheets are in use

TargetPageHeight = 29.7     ' A4 height in cm

' Don't change anything below here
TargetPageHeight = Application.CentimetersToPoints(TargetPageHeight)
TotalRowCount = NumberOfPages * RowsPerPage + 1 'Resize beyond the specified area so a smaller row doesn't sneak in
For sheetIndex = 1 To NumberOfSheets
AvailablePageHeight = TargetPageHeight
With Worksheets(sheetIndex).PageSetup
AvailablePageHeight = AvailablePageHeight - (.TopMargin + .BottomMargin + .HeaderMargin + .FooterMargin)
End With
OptimalRowHeight = AvailablePageHeight / RowsPerPage
currentIndex = 1
With Worksheets(sheetIndex)
While currentIndex <= TotalRowCount
Rows(currentIndex).RowHeight = OptimalRowHeight
currentIndex = currentIndex + 1
Wend
End With
Next sheetIndex

End Sub
• stylez, thank you for your extensive answer! I will definitely have a look at and test out your VBA sample. Apr 2, 2011 at 19:34

You can set the Excel so that it shows you the printing area. Check ribbon (View/Page Layout) Then what you have to do is to select all 31 rows and change the height of one of them.

• Amir, thanks for your comment, but how to I calculate the proper row height or 'drag-n-drop' it to the proper height? Additionally: When I set the "print area", this doesn't help. It displays it as "print area", but when I go in print preview it doesn't fit/scale! Plus (the problem is my Excel isn't in English!) the scale options in the print preview area do absolutely nothing! I tried to see a chance my selecting either of them, none showed any movement... Apr 2, 2011 at 18:29
• Actually now I understood what you meant: I can simply drag+drop one of the selected rows, changing the height by cautiously dragging the row downwards. Apr 2, 2011 at 19:33