Best fit print area

I have a sheet with several rows which, during the normal use of the sheet, may get expanded height-wise, basically meaning the sheet may get longer when a use fill it out. I'm trying to figure out how to set up a macro to make the sheet print in a best-fit sort of manner, and that would run at print-time.

To explain a little further, the sheet right now has a bunch of page breaks which I guess were automatic Excel breaks. So, if a user fills in very little information, then some of the pages will have just a few lines, and the rest blank.

There was a discussion on this here , but I'm curious if there's a way to make a macro just insert a page break every 'x' number of pixels, rounding to the nearest full row, until it reaches the end of the sheet.

Thanks.

OK, after inspecting the code from the other thread mentioned above, I've sort of fit it to my needs as follows.

Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
    nRows = ActiveSheet.Cells(1, 256).End(xlDown).Column
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 7, 0)    'number of pages long To Do
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesTall = i
        .FitToPagesWide = False
    End With
End Sub

This code now seems to make a "best-fit" for length. If I want the width fixed, say, coumns A through I, how would I modify this code?

Re: Best fit print area

After running the above code, I now can't move my righ-hand vertical page break beyond the right side of column H, but I need it at the right side of column I. Can anyone help?
Thanks.

Re: Best fit print area

OK, I've sorted out my latest issue (from my last post). I now have the following code. I wanted a confirm box before formatting the area. However, when I print, it just prints without popping up the message box. I don't know if this is a problem with my message box usage, or my BeforePrint usage. Can anyone tell me?
Incidentally, the FitPages sub has been tested and is working fine.
Thanks.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim response As String
    response = MsgBox("Format Costing Review before printing?", vbYesNoCancel, "Printing check")
    If response = vbYes Then
        Cancel = False
        Call FitPages
    ElseIf response = vbNo Then
        Cancel = False
        Exit Sub
    ElseIf response = vbCancel Then
        Cancel = True
    End If
End Sub




Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
    nRows = ActiveSheet.Cells(1, 256).End(xlDown).Column
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 10, 0)    'number of pages long To Do
    
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$117"
    
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = i
    End With
End Sub

Re: Best fit print area

Can anyone suggest why the BeforePrint method is not working for me? I've tried a simple Hello World msgBox, and even that does not work.

Re: Best fit print area

I don't get a ThisWorkbook in VBA. I know what you're talking about because I've used the ThisOutlookSession project in Outlook VBA before, but in my project explorer in Excel VBA, I just see the current VBA project, which is just named after the file I'm in, and another one from the Auto-Save Add-In.

How should I access ThisWorkbook?

Re: Best fit print area

OK, thanks for the tip. I'll work that in. Also, ignore my last question. I was looking for something that looked like a seperate project. I found ThisWorkbook.
Thanks again.

Re: Best fit print area

OK, I just spotted a problem with what I've done:

I only want this macro FitPages to run when the sheet "Project Review" is being printed, which will only occur if a normal print command is run when that sheet is active, or if the entire workbook is being printed. How would I work this in?

Re: Best fit print area

Nope. Something I'm trying to figure out is, can you call a print command from within the BeforePrint method, or will this always cause an infinite loop (since that new print command will itself call the BeforePrint (BP), etc.)?

The reason I ask is, I want some formatting changes to occur to the sheet "Projec Review" before printing, then be reversed after. There was another discussion about post-printing code here, but after thinking about the code I suggested, it seemed like it would make an infinite loop. The only way I can see of getting around this is if there's some more basic print command you can use in Excel that will ignore the BP method, so that you could call that from within BP.

Re: Best fit print area

Yeah, that's the same conclusion I've come to, and that's what I've done, with the print button being on the Project Review page. Now, the only other thing I'd like to do is if the entire workbook is being printed, that macro should run.

Re: Best fit print area

The code for printing a single sheet versus the whole book is as follows (sure you knew this already).

Sub printSingle()


    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
Sub printAll()


    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

So in light of there being two different commands, are there then two different versions of Before_Print for them?

Re: Best fit print area

OK, I've realized this thread has gotten way off topic from where I started initially. The code I have now to do the conditional printing is as follows, and was provided here by user royUK.

Following is a modified version of his code:

'---------------------------------------------------------------------------------------
 ' Module    : ThisWorkbook
 ' DateTime  : 19/10/2005 19:11
 ' Author    : Roy  Cox, edited to suit specific needs of this work book by Simon Silk
 ' Website   : www.excel-it.com
 ' Purpose   : stop print to run code. Then resume print
 '---------------------------------------------------------------------------------------
Option Explicit
 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True 'stop print
    
    Dim response As String
     
    response = MsgBox("Print just this sheet?" & Chr(10) & "Yes = print just current sheet" & Chr(10) & "No = Print entire workbook" & Chr(10) & _
"Click Cancel to cancel printing.", vbYesNoCancel, "Print What?")
    
    ' If only printing current page:
    If response = vbYes Then
        ' If printing Project Review:
        If (ActiveSheet.Name = "Project Review") Then
            Call unhighlightUserEntryCells
            Call FitPages
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            Call highlightUserEntryCells
        Else
        ' If printing any other sheet:
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        End If
    ' If printing entire workbook:
    ElseIf response = vbNo Then
        Call unhighlightUserEntryCells
        Call FitPages
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Call highlightUserEntryCells
    End If
    
    Application.EnableEvents = True
     
End Sub

This works fine for my needs, I'm just posting it so others can see.
Now I want to return to my original question about formatting for printing.

The following is the code I have, which is supposed to give a best-fit number of pages for the height of the document. However, it is unpredictable. Sometimes it will make good calls, like three pages or so for a document that realistically is around 3 pages long. Right now however, it attempts to cram about a 5-pager onto one page. Can anyone see a problem with this code?
Some of the individual rows in my sheet grow in height as the user fills in info, but I don't think that explains the problem here.

Sub FitPages()


Worksheets("Project Review").Activate


    Dim nRows As Integer, s As Double, i As Integer
    nRows = 117 ' Sheet currently only extends to row 117. Modify this as sheet is modified.
    
    s = 0
    
    For i = 1 To nRows
        s = s + Rows(i).Width
    Next i
    s = s / 72    'total inches long
    i = Application.Round(s / 10, 0)    'number of pages long To Do
    
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$117"
    
    With ActiveSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = i
    End With
End Sub

Re: Best fit print area

Nevermind. The problem with the formatting code was the line

s = s + Rows(i).Width

Should be

s = s + Rows(i).Height

I think that solves all my problems for now, at least related to the original question of this thread.

Thanks everyone for your help.