Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

Select the area you want printed.

then do

file=Set Print Area = Set Print Area

That should do it.

Regards,
Tom Ogilvy


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

Formulas cause blank pages to print
 

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.

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

Formulas cause blank pages to print
 

also asked in .misc
"Chris" wrote in message
...
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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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

Formulas cause blank pages to print
 

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






















.