Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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. ;)

Thanks for your help in advance!

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
    
stylez, thank you for your extensive answer! I will definitely have a look at and test out your VBA sample. –  grunwald2.0 Apr 2 '11 at 19:34
add comment

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.

share|improve this answer
    
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... –  grunwald2.0 Apr 2 '11 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. –  grunwald2.0 Apr 2 '11 at 19:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.