Can I keep rows together so they don't separate at a pagebreak?
 

I am creating a form in Excel. The rows may expand as the user enters data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to the next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

Can I keep rows together so they don't separate at a pagebreak?
 

Excel makes a very poor word processor.

I think you'll have to do this manually if you really need it.

On the other hand, MSWord can do light calculations within its tables. But
maybe your calculations are too serious???

Rita wrote:

I am creating a form in Excel. The rows may expand as the user enters data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to the next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

--

Dave Peterson

Can I keep rows together so they don't separate at a pagebreak?
 

Rita,

This can be done with a macro if all the row heights are the same, if you're
willing to get into that. I suppose it could be done where the row heights
are not the same too.

It might not be worth the macro effort, unless the sheet is very large. If
you do a print preview, it will put the page breaks on the sheet with dotted
lines. Or you can use View - Page Break Preview. Now that the page breaks
are visible, you can manually either put in and remove page breaks as needed
to keep the groups together, or insert rows, depending on how you want to
handle it.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
I am creating a form in Excel. The rows may expand as the user enters
data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to the
next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

Can I keep rows together so they don't separate at a pagebreak
 

Dave,
I know Excel is not a good word processor, but as I said in my original
post, I have serious calculations and formulas (lookups, if statements) so it
has to be done in Excel.

I am doing it manually now, I was looking for the possibility of a more
dynamic solution.

But thanks anyway.


"Dave Peterson" wrote:

Excel makes a very poor word processor.

I think you'll have to do this manually if you really need it.

On the other hand, MSWord can do light calculations within its tables. But
maybe your calculations are too serious???

Rita wrote:

I am creating a form in Excel. The rows may expand as the user enters data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to the next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

--

Dave Peterson

Can I keep rows together so they don't separate at a pagebreak
 

Earl
The row heights will not be the same. What would the macro do? How would I
tell it to keep the rows together?

I can't do it the seond way you suggest because it must be as automatic as
possible. There will be many different end users. I've put in a manual
break, which will work if no one gets too verbose. I just thought if there
was a (reasonably straightforward) way to automate it, that would be better.

Thanks for your reply.
Rita

"Earl Kiosterud" wrote:

Rita,

This can be done with a macro if all the row heights are the same, if you're
willing to get into that. I suppose it could be done where the row heights
are not the same too.

It might not be worth the macro effort, unless the sheet is very large. If
you do a print preview, it will put the page breaks on the sheet with dotted
lines. Or you can use View - Page Break Preview. Now that the page breaks
are visible, you can manually either put in and remove page breaks as needed
to keep the groups together, or insert rows, depending on how you want to
handle it.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
I am creating a form in Excel. The rows may expand as the user enters
data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to the
next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

Can I keep rows together so they don't separate at a pagebreak
 

Rita

Here's a macro that will move page breaks up to keep groups together,
Word-style.

Sub KeepTogether()
Const ColumnKey = 3 ' Key column for groups
Dim HCtr As Double
Dim i As Integer, j As Integer

ActiveSheet.ResetAllPageBreaks
HCtr = 0 ' Total row height accumulator for page
i = 1 ' row counter
Do While Cells(i, ColumnKey) < ""
HCtr = HCtr + Rows(i).Height ' accumulate height
i = i + 1 ' next row
If HCtr 650 Then ' one row past vertical height max
For j = i To 0 Step -1 ' go back through rows
If Cells(i, ColumnKey) < Cells(i - 1, ColumnKey) Then Exit For ' middle
of group, found a break, get out
i = i - 1
Next j
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
ColumnKey).EntireRow
HCtr = 0
End If ' HCtr
Loop
End Sub

Set ColumnKey to the column on which it's to group. It's hard-wired for 11"
paper with total top and bottom margin space of 2". It's barebones and not
very tested. If there's a group that is more than one page, I think it will
melt down. Once working, it would probably go in the Before_Print routine
in module ThisWorkbook.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
Earl
The row heights will not be the same. What would the macro do? How would
I
tell it to keep the rows together?

I can't do it the seond way you suggest because it must be as automatic as
possible. There will be many different end users. I've put in a manual
break, which will work if no one gets too verbose. I just thought if
there
was a (reasonably straightforward) way to automate it, that would be
better.

Thanks for your reply.
Rita

"Earl Kiosterud" wrote:

Rita,

This can be done with a macro if all the row heights are the same, if
you're
willing to get into that. I suppose it could be done where the row
heights
are not the same too.

It might not be worth the macro effort, unless the sheet is very large.
If
you do a print preview, it will put the page breaks on the sheet with
dotted
lines. Or you can use View - Page Break Preview. Now that the page
breaks
are visible, you can manually either put in and remove page breaks as
needed
to keep the groups together, or insert rows, depending on how you want to
handle it.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
I am creating a form in Excel. The rows may expand as the user enters
data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to
the
next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.

Can I keep rows together so they don't separate at a pagebreak
 

Wow! Thanks, I will give this a try.

Rita

"Earl Kiosterud" wrote:

Rita

Here's a macro that will move page breaks up to keep groups together,
Word-style.

Sub KeepTogether()
Const ColumnKey = 3 ' Key column for groups
Dim HCtr As Double
Dim i As Integer, j As Integer

ActiveSheet.ResetAllPageBreaks
HCtr = 0 ' Total row height accumulator for page
i = 1 ' row counter
Do While Cells(i, ColumnKey) < ""
HCtr = HCtr + Rows(i).Height ' accumulate height
i = i + 1 ' next row
If HCtr 650 Then ' one row past vertical height max
For j = i To 0 Step -1 ' go back through rows
If Cells(i, ColumnKey) < Cells(i - 1, ColumnKey) Then Exit For ' middle
of group, found a break, get out
i = i - 1
Next j
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Cells(i,
ColumnKey).EntireRow
HCtr = 0
End If ' HCtr
Loop
End Sub

Set ColumnKey to the column on which it's to group. It's hard-wired for 11"
paper with total top and bottom margin space of 2". It's barebones and not
very tested. If there's a group that is more than one page, I think it will
melt down. Once working, it would probably go in the Before_Print routine
in module ThisWorkbook.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
Earl
The row heights will not be the same. What would the macro do? How would
I
tell it to keep the rows together?

I can't do it the seond way you suggest because it must be as automatic as
possible. There will be many different end users. I've put in a manual
break, which will work if no one gets too verbose. I just thought if
there
was a (reasonably straightforward) way to automate it, that would be
better.

Thanks for your reply.
Rita

"Earl Kiosterud" wrote:

Rita,

This can be done with a macro if all the row heights are the same, if
you're
willing to get into that. I suppose it could be done where the row
heights
are not the same too.

It might not be worth the macro effort, unless the sheet is very large.
If
you do a print preview, it will put the page breaks on the sheet with
dotted
lines. Or you can use View - Page Break Preview. Now that the page
breaks
are visible, you can manually either put in and remove page breaks as
needed
to keep the groups together, or insert rows, depending on how you want to
handle it.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Rita" wrote in message
...
I am creating a form in Excel. The rows may expand as the user enters
data.
Is there any way to keep each "section" in the form together, so that a
ssection does not split at a page break but jumps in its entirety to
the
next
page? I know I can do this in Word, but this form has lots of serious
calculations so it must be in Excel.