Formulas cause blank pages to print

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have developed a Timesheet that uses Data Validation
fields and If Then statements. To allow our managers to
have plenty of rows, I have extended the range of rows
with the formulas down about 300 rows. Unfortunately,
the existence of the the If then statements, which refer
to the cells being "validated" causes the print area to
extend well beyond the actual data, thus printing many
blank pages.

I have tried to find a way to limit the print area to the
actual data but unable. Can anyone help? Many thanks in
advance. I will be glad to send a copy of the
spreadsheet.

Chris C
 
Select the area you want printed.

then do

file=>Set Print Area => Set Print Area

That should do it.

Regards,
Tom Ogilvy
 
Hi,

You could set a range that was as large as the area you
wanted to print and then just print the range. Not the
fanciest solution, but it would work.

John.
 
Chris

if you want code, you could try this:

Sub SetPrintArea()
ActiveSheet.PageSetup.PrintArea = "=OFFSET(A1,,,COUNTA(A:A),3)"
ActiveSheet.PrintPreview
End Sub

It doesn't actually leave the formula in place; it works out the range and
sets the print area. Adjust the 3 at the end to the number of columns.
There may be other ways (probably lots) but this is the best I could come up
with.

You could drop this code into the Workbook_BeforePrint event code for no
manual intervention.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.PrintArea = "=OFFSET(A1,,,COUNTA(A:A),3)"
End Sub

This reduced my test from 103 pages (in preview) to just 1.

Regards

Trevor
 
Why do you say it doesn't leave the formula in place - it does and only
would need to be run once.

The formula itself should then adequately define the print area for entries
in column A (assuming no interspersed blank cells). I have used this
technique in the past (the formula, not the code), to determine both the
rows and columns - and it works well. Like any defined name, Print_Area can
have a formula in Refersto.
 
Tom

I didn't even know that I could do this until I tried it. I go to Page
Setup and select Sheet and drop the formula into the Print Area box, with or
without the equals sign. Then I select Print Preview and view the page.
When I go back to the Page Setup dialogue, low and behold the Print Area is
shown as a range, for example A1:C9.

I've just checked the Defined Name for Print_Area and it shows:
=Sheet1!$A$1:$C$9

following your reply, I've tried Defining the Name as an alternative
mechanism but I get the full 103 pages.
=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A),3) although I didn't put in the
Sheet1! in the references.

Hence the reason for providing the code.

Regards

Trevor
 
Well, I didn't crawl around in your code, just the concept.

Worksheets("Sheet1").Names.Add Name:="Print_Area", _
Refersto:= "=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),10)"

Works fine for me, putting in a permant defined name formula (until deleted
or changed) that dynamically determines the print area for sheet1 based on
contiguous entries in column A and a specified number of columns.

As always, defined names, in general should have sheet references and be
absolute.
 
Instead of doing this from the page setup>goto insert>name>define>delete
Print_Area>add Print_Area.
=offset($a$4,0,0,counta($a:$a),7)
you will find that the active sheet name will be inputed for you
automatically.
Save
try it
 
I think the trick may be to do it from the insert menu and to delete the OLD
Print_Area first.
nite nite, sleep tite.
 
the code I put up is doing it from the Insert Menu, not from the page setup.

And nice hint on the sheet names, but I think I will always type them in.

Anyway, for Trevor, with the defined name Print_Area Sheet1

Refers to:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),10)

and pagebreaks displayed, (not pagebreak view, however)

each time I add a filled cell in Column A, the dotted lines change to
include it in the area to print.

in pagebreak view, it is even more "dramatic". type in column A in the
first row in the GRAY area and the pagebreak expands to include it.

xl97 this time, but xl2000 previously.
 
Tom/Don

finally got there thanks to the way you described the effect of adding new
lines. If I use Define | Name and input the formula or use Page Setup it
does put the formula into the name. And so it stays, provided I don't look
at it through Page Setup, at which point it becomes a fixed range. Please
don't tell me this doesn't happen to you !

Regards

Trevor
 
You are correct, going into pagesetup replaces it with a hard coded range.

Never noticed that before. Guess that is why you were getting a temporary
result.
 
So. You're the one who started this madness. <G>

Chris C. said:
To Trevor, Tom and Don.

I took Trevor's suggestion, put into a macro, assigned
the macro to a custom menu and it works great.

I have to tell you guys, it's pretty cool to watch guys
that really know their stuff, make suggestions back and
forth.

Thanks for the effort you put into solving a problem for
a total stranger.

Sincerely,
Chris Cantele
Click to expand...
 
Back
Top