Page Breaks - Can they be ignored?

[Guest]

Helo Folk's

If I have set my pages breaks in an excel worksheet that shows for example
10 pages, can excel ignore page breaks that have no information on them.

For example, all 10 pages hold question to which users insert their
responses. If say pages 5 and 6 have been left blank, can I (when printing)
ignore these two pages so that only pages 1-4 and 7-10 print out. I must
have the page breaks including pages 5 and 6 in case they are used but I
don't want to print them if they are unanswered.

To add to this problempages 5 and 6 if not required would be hidden (by way
of a simple macro) but when I print out the worksheet, a blank page for each
of the hidden page 5 and 6 still come through.

So in short I want excel to ignore any hidden pages form the page breaks.

This question has been post before but worded completely differently so I'm
hoping I'm not confusing anyone here.

Regards,

Pat Convey.

 

[Jim Rech]

Excel does not ignore manual page breaks even if the row they are on is
hidden.

I'd suggest a different approach, assuming you can use macros - create a
print range that includes just the pages with data.

Here is an example where there are 4 pages with a range name Page_1, etc.,
assigned to each page. The macro counts the cells with data on each page
and only includes those where the count is greater than 0 in the print
range.

Sub PrintPagesWithData()
Dim PrnRg As Range
Dim SheetRg As Range
Dim Counter As Integer
For Counter = 1 To 4
Set SheetRg = Range("Page_" & Counter)
If Application.CountA(SheetRg) > 0 Then
If PrnRg Is Nothing Then
Set PrnRg = SheetRg
Else
Set PrnRg = Union(PrnRg, SheetRg)
End If
End If
Next
If Not PrnRg Is Nothing Then PrnRg.PrintPreview
End Sub


--
Jim
| Helo Folk's
|
| If I have set my pages breaks in an excel worksheet that shows for example
| 10 pages, can excel ignore page breaks that have no information on them.
|
| For example, all 10 pages hold question to which users insert their
| responses. If say pages 5 and 6 have been left blank, can I (when
printing)
| ignore these two pages so that only pages 1-4 and 7-10 print out. I must
| have the page breaks including pages 5 and 6 in case they are used but I
| don't want to print them if they are unanswered.
|
| To add to this problempages 5 and 6 if not required would be hidden (by
way
| of a simple macro) but when I print out the worksheet, a blank page for
each
| of the hidden page 5 and 6 still come through.
|
| So in short I want excel to ignore any hidden pages form the page breaks.
|
| This question has been post before but worded completely differently so
I'm
| hoping I'm not confusing anyone here.
|
| Regards,
|
| Pat Convey.

 

[Guest]

Hello Jim,

Thanks for the reply.

I'll think I'll have a dabble at your suggestions and see what comes out.

Many thanks once again.

Pat Convey.