About Us

Don Guillett[_4_]

So. You're the one who started this madness. <G

"Chris C." wrote in message
...
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

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote
in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

Don Guillett wrote in message
...
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.

"Trevor Shuttleworth"
wrote in message
...
Don

thanks for your suggestion ... but ... you know
what I'm going to
say
...
just the same ! Time for bed, I think.

It's not life or death, it's just frustrating
now that it works
differently
for me and I don't understand why.

Ah well

Trevor

"Don Guillett" wrote in message
...
Instead of doing this from the page setupgoto
insertnamedefinedelete
Print_Areaadd 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

"Trevor Shuttleworth"
wrote in message
...
Tom

thanks for your efforts but this just does
not want to work for
me.
I've
copied the code below and run it exactly as
is with the same
result
...
except in this case: =Sheet1!$A$1:$J$9

I give in ! I'm using Excel 2000 SP 3 under
Windows 2000 SP 3

Regards

Trevor

"Tom Ogilvy" wrote in
message
...
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.

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth"
wrote in
message
news:uo%
...
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

"Tom Ogilvy" wrote in
message

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

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth"
wrote in
message

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

"Chris"
wrote in message
news:016101c37179$9b8804f0
...
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

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Print preview/page set-up is printing 100's of blank pages	edwin	Excel Discussion (Misc queries)	2	March 6th 10 12:46 PM
How do I delete pages in Excel? Keeps printing blank pages at end.	Jojobean	Charts and Charting in Excel	1	May 31st 07 07:37 AM
Pages print blank?	Tricia K.	Excel Discussion (Misc queries)	1	January 24th 07 12:56 AM
How do I get Excel to not print blank pages when using outlines	Excel Challenged	Excel Worksheet Functions	1	August 24th 06 10:26 PM
how do I print a range without printing the blank pages?	Chris VP	Excel Discussion (Misc queries)	2	May 2nd 05 07:08 PM