Printing one or more pages?

[Dean Southgate]

I have a spreadsheet that pulls info into a report. This
report is
usually only a few lines long and prints onto one page of
paper
without any problem.

Sometimes however there is enough info to go over the
page.
Currently I have formula on each line of the report page
that gets the
info, if there is no info the line is left blank. Is
there any way to
fill the report down three or four pages with these
formula but only
print the one or two pages that have info or can anyone
suggest
another way of doing it.

Page one

Report Header
....
...

Line of formula, pulled from page two if there is data to
pull
Line of formula, pulled from page two if there is data to
pull
Line of formula, pulled from page two if there is data to
pull
Line of formula, pulled from page two if there is data to
pull
..
..
..

Down to the full fourty lines of data for 1 page.
Sometimes I need forty five or sixty or seventy lines but
this will
print blank pages if I fill the formula down.

A VBA program would be acceptable if possible?

Any ideas?


Regards Dean
(e-mail address removed) <mailto:[email protected]>
www.dkso.co.uk/ <http://www.dkso.co.uk/>
http://homepage.ntlworld.com/dkso

 

[Tom Ogilvy]

go to the VBE and in the project explorer, select ThisWorkbook entry for
your project. Double click on it to get to the thisworkbook module. Put in
code like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rng As Range, i As Long, lastrow As Long
If ActiveSheet.Name = "Report" Then
Set rng = Cells(Rows.Count, 1).End(xlUp)
For i = rng.Row To 1 Step -1
If Cells(i, 1) <> "" Then
lastrow = i
Exit For
End If
Next
ActiveSheet.PageSetup.PrintArea = _
Range(Cells(1, 1), Cells(lastrow, 10)).Address( _
External:=True)
End If
End Sub

adjust Report to reflect the name of your report sheet and 10 to reflect the
number of columns to print.