Excel 2007 - Grouped Rows and Hidden Pages. How to exclude from Page Numbering and Printing?

[hjgoldstein]

Hi all,

I have a problem with page numbering and printing.

Long story short...

My Worksheet references others and often a page has no data to print. When this happens I group the rows of the affected page and hide them. I don't delete the rows as I may need it in a subsequent version of the worksheet. There is a footer with page numbering "Page n of n"

Previously, when either printing or PDFing (same thing as far as I am concerned) the hidden pages were excluded in the printing, the page numbering and the total page count. Now, for some reason, the page count includes the count of hidden pages, the page numbering includes them and a print preview shows them as blank pages.

Is ther a simple setting I can use to restore the previous régime?

Thanks for any input.

Aspiring to mediocrity since 1957

 

[SkipVought]

HI,

What do you mean by hidden PAGES? Excel doesn't have pages that you can hide. You can hide a SHEET.

What do you mean by PREVIOUSLY? Previous to what set of conditions? What changed?

So are you printing multiple SHEETS? And some sheets have no data in that print cycle?

Please answer ALL these questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[hjgoldstein]

OK, bad terminology on my part.

I meant that I group rows and hide them. It is the hidden rows that constitute one hidden page - page break at the start and at the end.

Previously means that thid didn't happen last time I did it. Nothing has changed as far as I know.

No, I am printing one sheet only.

It is the hidden rows that are appearing in the print but I want to exclude them.

Aspiring to mediocrity since 1957

 

[SkipVought]

Please remember that we can't see what's on your screen or what's in your head.

So are you in Page Break display mode?

If so is your report set up 1 page wide and X pages long?

Are your "hidden" rows actually hidden or do you mess with the Row Height? On that topic, I don't think I would GROUP anything, just simply Select the rows for that page and Hide them.

Do you have any HARD Page Breaks?





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[hjgoldstein]

Yes, I am in page break display mode

No, the print area is set to print only the columnd I require.

The hidden rows are hidden. Row height is not touched.

I tried removing the grouping and hiding the rows and the same happened.

There are no hard page breaks.

Aspiring to mediocrity since 1957

 

[SkipVought]

Do you have any macros in your workbook? Maybe in the Workbook_BeforePrint event?

Short of these things, last thing might be to upload your workbook and let me hands-on see what's going on.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[hjgoldstein]

No macros but plenty of formulae.

Sorry I can't upload as it is all highly sensitive data and too much to anonymize.

I'll try other stuff.

Thanks for the input.

Aspiring to mediocrity since 1957

 

[SkipVought]

Copy the sheet to a new workbook.

I ONLY need to see a page to print and a hidden page that still prints. Everything else can be deleted.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[hjgoldstein]

Sorry, no can do. thanks anyway.

Aspiring to mediocrity since 1957

 

[SkipVought]

You really never answered this question before:

If so is your report set up 1 page wide and X pages long?

So what I mean is in your Page Break view, do you only have HORIZONTAL Page Breaks and no VERTICAL Page Breaks? In other words do you have only Horizontal dotted lines as Page Breaks and no Vertical dotted lines as Page Breaks

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

hjgoldstein,

Can you re-create your issues with fresh Excel with some bogus data in it?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

There's something difficult for us to understand.

Let's say you have 3 pages in Page Break view.

You HIDE the rows for page 2.

Now you see page 1 & page 2 in Page Break view, and no more.

But when you do a Print Preview, you see 3 pages, and page 2 is blank?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[hjgoldstein]

Skip,
In response to your last post.
I have 3 pages in Page Break View.
I hide the rows for Page 2
Now I see Page 1 and Page 3 in Page Break View and Page 2 displays a blank page in Print preview.

Andy,
I will try that when time permits.

Thanks.

Aspiring to mediocrity since 1957

 

[combo]

You have hard page breaks and they are hidden with row. This results empty pages for hidden areas in print preview. To remove those pages (no need to unhide rows):
- right-click sheet and reset page breaks,
- set again hard page breaks if necessary.

combo

 

[hjgoldstein]

Combo, I tried that too. No joy.

Skip, where can I upload a file to? I just created a new workbook with next to no data and the same thing happens.

Thanks for your patience

Aspiring to mediocrity since 1957

 

[Andrzejek]

When you type a post or a replay, under the text box you have:
Attachment:
Click Here to upload your file to ENGINEERING.com (link)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[hjgoldstein]

Thanks Andy.

I really should read these things before I post

Here is the file I created.

Aspiring to mediocrity since 1957

 

[combo]

Did you removed ALL page breaks from the popup menu and added new having rows hidden?

combo

 

[hjgoldstein]

Yes I did. The attached is a new file which started behaving in exactly the same way immediately.

Aspiring to mediocrity since 1957

 

[SkipVought]

Here's what I did:

1) Remove grouping. Rather simply select all rows for a given page and Right-Click HIDE

2) Reset Page Breaks. Rather use the Natural page breaks (dotted breaks) for all pages.

Result: 3 pages rather than 4.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]