I notice that, when I ask EXCEL to print an exhibit on 2 pages, it decides
what to put on page 1 and page 2 and that page 1 always seems to have more
data on it than page 2. I have tried altering either top and bottom margins
or header and footer, to try to get it to put a bit more (but still never
even quite half) on page 2. But it stubbornly won't change anything. All
it does is shrink the size of the image on both pages, still leaving more
than half the printed range on page 1. Both have the same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is there some way to get
EXCEL to spread the data more evenly across these two pages? It's almost
as if there is something at the bottom of a "final" page that it thinks it
must leave extra room for? Please don't suggest a solution that will print
out, say, a blank 3rd page.

Thanks
Grace

Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit on 2
pages, it decides
what to put on page 1 and page 2 and that page 1 always
seems to have more
data on it than page 2. I have tried altering either top
and bottom margins
or header and footer, to try to get it to put a bit more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both pages,
still leaving more
than half the printed range on page 1. Both have the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these two
pages? It's almost
as if there is something at the bottom of a "final" page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.

Nope. Actually, I never used that feature! Works very nicely! Is it true
that, if you're smart enough, you can make EXCEL clean windows!

Only problem is that, currently, based on the total number of rows, the
number of pages is computed by a macro and I would need this resetting of
page breaks to be controlled by macro, too.

Ideally, I would like it to break the page only after (three plus) a
multiple of 5 rows. Since it seems to want to put more rows on page 1 than
page 2 (and, if there are 3 pages, more on two than three, I'd guess), I
think I would want it to round down to the nearest multiple of five. If it
turns out to need 1, 2, 3, or 4 pages (max), is there a way to have a macro
tell it always to round down from what it automatically computes, and
(re-)place the page breaks there?

Can anyone give me the macro code for that?

Thanks so much,
Grace

"DavidC" wrote in message
...
Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit on 2
pages, it decides
what to put on page 1 and page 2 and that page 1 always
seems to have more
data on it than page 2. I have tried altering either top
and bottom margins
or header and footer, to try to get it to put a bit more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both pages,
still leaving more
than half the printed range on page 1. Both have the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these two
pages? It's almost
as if there is something at the bottom of a "final" page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.

You can set the page break by VBA above a specified row.
What you need to do therefore, is:
find how many rows you have,
divide by 5 and set this value to a variable.
set the page break,
do a test to see if there are more rows left and repeat
until the number of rows =0.
this sets the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual


A code like this should do the job:

Sheets("sheet1").Range("A65536").Select
Selection.End(xlUp).Select
row1 = ActiveCell.Row

numrows = row1 - 1 'sets number of rows of data to select
from

b = 0
a = Round(numrows / 5, 0)
For n = 1 To a
b = b + 5

Worksheets("Sheet1").Rows(b).PageBreak = xlPageBreakManual
Next

change 5 to whatever value you want to have as the number
of rows on a page.

this code removes the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNone
again replacing 25 with the row number where the page
break occurs. Best this is done before updating the
workbook to ensure that you find the page breaks.

-----Original Message-----
Nope. Actually, I never used that feature! Works very
nicely! Is it true
that, if you're smart enough, you can make EXCEL clean
windows!

Only problem is that, currently, based on the total
number of rows, the
number of pages is computed by a macro and I would need
this resetting of
page breaks to be controlled by macro, too.

Ideally, I would like it to break the page only after
(three plus) a
multiple of 5 rows. Since it seems to want to put more
rows on page 1 than
page 2 (and, if there are 3 pages, more on two than
three, I'd guess), I
think I would want it to round down to the nearest
multiple of five. If it
turns out to need 1, 2, 3, or 4 pages (max), is there a
way to have a macro
tell it always to round down from what it automatically
computes, and
(re-)place the page breaks there?

Can anyone give me the macro code for that?

Thanks so much,
Grace

"DavidC" wrote in
message
...
Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit on
2
pages, it decides
what to put on page 1 and page 2 and that page 1 always
seems to have more
data on it than page 2. I have tried altering either
top
and bottom margins
or header and footer, to try to get it to put a bit
more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both pages,
still leaving more
than half the printed range on page 1. Both have the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these two
pages? It's almost
as if there is something at the bottom of a "final"
page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.



.

It seems like you are suggesting that I remove the page breaks first. I
assume EXCEL has some magic algorithm for setting its page breaks, something
that will vary as my data varies (which it does). How can I make my macro
remove them, if they will vary? Isn't there a way to just tell it to remove
all page breaks wherever they are? Wouldn't that work nicely?

Thanks,
Grace

"DavidC" wrote in message
...
You can set the page break by VBA above a specified row.
What you need to do therefore, is:
find how many rows you have,
divide by 5 and set this value to a variable.
set the page break,
do a test to see if there are more rows left and repeat
until the number of rows =0.
this sets the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual


A code like this should do the job:

Sheets("sheet1").Range("A65536").Select
Selection.End(xlUp).Select
row1 = ActiveCell.Row

numrows = row1 - 1 'sets number of rows of data to select
from

b = 0
a = Round(numrows / 5, 0)
For n = 1 To a
b = b + 5

Worksheets("Sheet1").Rows(b).PageBreak = xlPageBreakManual
Next

change 5 to whatever value you want to have as the number
of rows on a page.

this code removes the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNone
again replacing 25 with the row number where the page
break occurs. Best this is done before updating the
workbook to ensure that you find the page breaks.

-----Original Message-----
Nope. Actually, I never used that feature! Works very
nicely! Is it true
that, if you're smart enough, you can make EXCEL clean
windows!

Only problem is that, currently, based on the total
number of rows, the
number of pages is computed by a macro and I would need
this resetting of
page breaks to be controlled by macro, too.

Ideally, I would like it to break the page only after
(three plus) a
multiple of 5 rows. Since it seems to want to put more
rows on page 1 than
page 2 (and, if there are 3 pages, more on two than
three, I'd guess), I
think I would want it to round down to the nearest
multiple of five. If it
turns out to need 1, 2, 3, or 4 pages (max), is there a
way to have a macro
tell it always to round down from what it automatically
computes, and
(re-)place the page breaks there?

Can anyone give me the macro code for that?

Thanks so much,
Grace

"DavidC" wrote in
message
...
Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit on
2
pages, it decides
what to put on page 1 and page 2 and that page 1 always
seems to have more
data on it than page 2. I have tried altering either
top
and bottom margins
or header and footer, to try to get it to put a bit
more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both pages,
still leaving more
than half the printed range on page 1. Both have the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these two
pages? It's almost
as if there is something at the bottom of a "final"
page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.



.

Can't get the suggested code in 'Help' to work. Excel
certainly does automatically set page breaks, and they are
only overridden by page breaks set manually (or through
code) The idea then is before updating the sheet and
having different numbers of rows to when the page break
was first applied, run the code to remove those page
breaks and effectively start with a clean sheet.

Sorry but the best I can suggest at this stage.

Best of Luck
DavidC
-----Original Message-----
It seems like you are suggesting that I remove the page
breaks first. I
assume EXCEL has some magic algorithm for setting its
page breaks, something
that will vary as my data varies (which it does). How
can I make my macro
remove them, if they will vary? Isn't there a way to
just tell it to remove
all page breaks wherever they are? Wouldn't that work
nicely?

Thanks,
Grace

"DavidC" wrote in
message
...
You can set the page break by VBA above a specified row.
What you need to do therefore, is:
find how many rows you have,
divide by 5 and set this value to a variable.
set the page break,
do a test to see if there are more rows left and repeat
until the number of rows =0.
this sets the page break
Worksheets("Sheet1").Rows(25).PageBreak =
xlPageBreakManual


A code like this should do the job:

Sheets("sheet1").Range("A65536").Select
Selection.End(xlUp).Select
row1 = ActiveCell.Row

numrows = row1 - 1 'sets number of rows of data to
select
from

b = 0
a = Round(numrows / 5, 0)
For n = 1 To a
b = b + 5

Worksheets("Sheet1").Rows(b).PageBreak =
xlPageBreakManual
Next

change 5 to whatever value you want to have as the
number
of rows on a page.

this code removes the page break
Worksheets("Sheet1").Rows(25).PageBreak =
xlPageBreakNone
again replacing 25 with the row number where the page
break occurs. Best this is done before updating the
workbook to ensure that you find the page breaks.

-----Original Message-----
Nope. Actually, I never used that feature! Works very
nicely! Is it true
that, if you're smart enough, you can make EXCEL clean
windows!

Only problem is that, currently, based on the total
number of rows, the
number of pages is computed by a macro and I would need
this resetting of
page breaks to be controlled by macro, too.

Ideally, I would like it to break the page only after
(three plus) a
multiple of 5 rows. Since it seems to want to put more
rows on page 1 than
page 2 (and, if there are 3 pages, more on two than
three, I'd guess), I
think I would want it to round down to the nearest
multiple of five. If it
turns out to need 1, 2, 3, or 4 pages (max), is there a
way to have a macro
tell it always to round down from what it automatically
computes, and
(re-)place the page breaks there?

Can anyone give me the macro code for that?

Thanks so much,
Grace

"DavidC" wrote in
message
...
Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit
on
2
pages, it decides
what to put on page 1 and page 2 and that page 1
always
seems to have more
data on it than page 2. I have tried altering
either
top
and bottom margins
or header and footer, to try to get it to put a bit
more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both
pages,
still leaving more
than half the printed range on page 1. Both have
the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these
two
pages? It's almost
as if there is something at the bottom of a "final"
page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.



.



.