Excel 2007/2010, Macro to set print area and choose number of pages based on total sheet height


  • Hi there,

    I have searched TechNet and other forums for an answer to this but have had no joy.

    I am creating a workbook that captures data on a few different worksheets and combines it all into one review sheet. The workbook is very important as it tracks the use and movements of hundreds of explosives as used in the oil and gas industry.

    I have a macro to adjust various rows' heights based on a reference cell value. (if the reference cell value is 0 or "" then the row height is 0).

    The problem this causes is, when I want to print the page or export to PDF, it will still print one page. The more rows that are used, the smaller the text in the print out will appear.

    Does anyone have suggetions for a macro that will change a print area and number of pages to print automatically?

    If I was using the workbook for myself, I would spend the time to sort everything out manually. In this case, the workbook will be used by as many as 80 users, this means I would like as many features to be automatic.

    I thank you in advance for any advise you can offer.


    ps. I just tested the following code for changing row heights and found a problem. What I aim to do is; if any cell in column C 16:C31 have a value of 0 (the value is resulting from an IF function from a reference Sheet) then that row will have a height of 0. If I enter data into the reference sheet that changes the value of any cell in C16:C31 to "not 0" I want the row height to return to normal:

    Current Code below.

    Sub HideRows()

    BeginRow = 16
        EndRow = 30
        ChkCol = 3
            For RowCnt = BeginRow To EndRow
                If Cells(RowCnt, ChkCol).Value = 0 Then
                    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
                ElseIf Cells(RowCnt, ChkCol).Value <> 0 Then
                    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
                End If
        Next RowCnt

    End Sub

    This only works when I select "Run" in Visual Basic. So if I have a cell with value "0" but then update the input cell to change the value, the row height does not return to normal. If I could be advised on this also, please help.


    Yes, I am very new to Visual Basic but a fast learner..



    • Edited by derekjspence Wednesday, September 14, 2011 6:26 AM
    Wednesday, September 14, 2011 12:55 AM

All replies

  • From your description, it sounds like your page height is set to 1 page.  This causes excel to scale the content to fit to one page instead of leaving the content at it's original size and printing across multiple pages.

    In Excel 2007, you can check this setting on in the ribbon on the "Page Layout" tab.  Under the "Scale to Fit" section you will have Width, Height, and Scale settings.  If you want it to maintain it's print size and use as much paper as necessary, both Width and Height should be set to Automatic.  Scale should be 100%.  If you just set one of the options, for example width to 1 page.  It will scale the document so that it's only 1 page wide, then print as many pages as the document is long for height based on the new scaled size.

    For Excel 2003 this setting is under file page setup.

    Wednesday, September 14, 2011 1:21 PM
  • SitrucHtims


    Thanks for the response. You are right about the scaling settings. I hope I am clearer with this reply.

    The document I am creating has the possibility of having very little or quite a lot of info (in this case rows 16 to 31). Where only a few rows are used, I would like the document to print one page. When a lot of rows are populated, I would like two pages to print.

    I have a header and a footer that are populated by reference calculations. The idea is to have the footer appear only at the bottom of the last page and the header only on the top of the first.

    If I write a macro to hide rows where the value in column c is zero, the footer will drag up. I could write a macro to increase a "spacer" row's height depending on how many rows have zero value. Hopefully this will allow me to tell it to position the footer at the very bottom of whichever is the last page on the document.

    Hope this makes sense.


    Thanks Again

    Thursday, September 15, 2011 2:07 PM
  • Derek,

         I know it's been a while, and not sure if you have resolved this yet or not, but here is another option I have discovered.  Hope it helps.  Basically it's a macro that copies the contents of a row or rows of data into the footer section of the page setup.


    Sub MyFooter()
    Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
    Set Sh = Worksheets("Sheet5")
    Set Rng = Sh.Range("A55:G55")

    For Each c In Rng
    StrFtr = StrFtr & c & " "
    Next c

    ActiveSheet.PageSetup.LeftFooter = StrFtr
    End Sub

    Friday, January 13, 2012 1:02 PM