Blank Page Printing

Hi All,

I have an unusual take on an old problem. I have an Excel spreadsheet
that is printing an extra blank page. I have seen the many many posts
on this but all attempts to correct it have failed.

My data is in the range A1:F55. When I use Ctrl End, the activecell is
F55. I have deleted and cleared all columns G to IV and rows 56 to
65536. I have used VBA to reset the Used Range but still I get a
second blank page.

If I set Print Row & Column headings I can see that the second page is
to the right of my data. It is trying to print columns H to J ie an
extra 4 columns. If I try to set the sacling to 1 wide and blank tall,
the data prints on one page but is compressed as it tries to squeeze
the extra 4 columns onto the page. Obviously there is something
happening in the last 4 columns but I cannot find it.

Any ideas anyone?

Regards

Glyn Baker

Hi Glyn,
I suspect you have cells with a large number of spaces
or non-breaking spaces on the right side of the cells.
Or possible a shape (image on the page). Did you copy
the data from a web page.

Make a copy of your worksheet:

See if this makes a difference:
Ctrl+A to select all cells on page and then run the
TrimALL macro found on
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Try deleting all shapes on the page, then do Print Preview:

Sub delShapesOnSht()
'Dave Peterson, misc 2001-11-12, no loop required
If ActiveSheet.Shapes.Count = 0 Then
MsgBox "No Shapes on page for deletion"
Exit Sub
End If
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes
Selection.Delete
End Sub

If that worked and you do have some shapes you want to keep
there are additional macros on my shapes.htm webpage.

The following would appear to do nothing for you since you
indicate that F55 shows up as your lastcell (Ctrl+End)
but this is what I normally use for a single page.

Select the cell you want to be the last cell (F55)

The MakeLastCell macro on my page:
http://www.mvps.org/dmcritchie/excel...m#makelastcell
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Glyn Baker" <> wrote in message news: om...
> Hi All,
>
> I have an unusual take on an old problem. I have an Excel spreadsheet
> that is printing an extra blank page. I have seen the many many posts
> on this but all attempts to correct it have failed.
>
> My data is in the range A1:F55. When I use Ctrl End, the activecell is
> F55. I have deleted and cleared all columns G to IV and rows 56 to
> 65536. I have used VBA to reset the Used Range but still I get a
> second blank page.
>
> If I set Print Row & Column headings I can see that the second page is
> to the right of my data. It is trying to print columns H to J ie an
> extra 4 columns. If I try to set the sacling to 1 wide and blank tall,
> the data prints on one page but is compressed as it tries to squeeze
> the extra 4 columns onto the page. Obviously there is something
> happening in the last 4 columns but I cannot find it.
>
> Any ideas anyone?
>
> Regards
>
> Glyn Baker

Since Mr McR has already given a long and complicated response I can only
assume I'm missing something, but is the obvious answer not to check if a
print area has been set? If it has, that would explain both the extra blank
page and the fact the scaling to 1x1 causes the genuine data to be squashed
up.

Possibly

Paul C,
--

Glyn Baker wrote:
> Hi All,
>
> I have an unusual take on an old problem. I have an Excel spreadsheet
> that is printing an extra blank page. I have seen the many many posts
> on this but all attempts to correct it have failed.
>
> My data is in the range A1:F55. When I use Ctrl End, the activecell is
> F55. I have deleted and cleared all columns G to IV and rows 56 to
> 65536. I have used VBA to reset the Used Range but still I get a
> second blank page.
>
> If I set Print Row & Column headings I can see that the second page is
> to the right of my data. It is trying to print columns H to J ie an
> extra 4 columns. If I try to set the sacling to 1 wide and blank tall,
> the data prints on one page but is compressed as it tries to squeeze
> the extra 4 columns onto the page. Obviously there is something
> happening in the last 4 columns but I cannot find it.
>
> Any ideas anyone?
>
> Regards
>
> Glyn Baker

Hi Paul,
You are absolutely correct. Having a Print Area would
be the more probable cause.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Paul Cundle" <> wrote in message news:OA$...
> Since Mr McR has already given a long and complicated response I can only
> assume I'm missing something, but is the obvious answer not to check if a
> print area has been set? If it has, that would explain both the extra blank
> page and the fact the scaling to 1x1 causes the genuine data to be squashed
> up.
>
> Possibly
>
> Paul C,
> --
>
> Glyn Baker wrote:
> > Hi All,
> >
> > I have an unusual take on an old problem. I have an Excel spreadsheet
> > that is printing an extra blank page. I have seen the many many posts
> > on this but all attempts to correct it have failed.
> >
> > My data is in the range A1:F55. When I use Ctrl End, the activecell is
> > F55. I have deleted and cleared all columns G to IV and rows 56 to
> > 65536. I have used VBA to reset the Used Range but still I get a
> > second blank page.
> >
> > If I set Print Row & Column headings I can see that the second page is
> > to the right of my data. It is trying to print columns H to J ie an
> > extra 4 columns. If I try to set the sacling to 1 wide and blank tall,
> > the data prints on one page but is compressed as it tries to squeeze
> > the extra 4 columns onto the page. Obviously there is something
> > happening in the last 4 columns but I cannot find it.
> >
> > Any ideas anyone?
> >
> > Regards
> >
> > Glyn Baker
>
>

You might try previewing the printed page and turning the margins on in the
previewing window. If you don't see all of your rows and columns there (on
one page), grab the column marker closest to the right margin, and drag it
to the right. This has worked for me when I had the problem you described.
Good luck.

Thanks for all your responses.

I did not have a print area set but there were a lot of trailing
spaces after the text in one of the cells. The data was downloaded
from an HP3000 manufacturing system as a text file and then imported
into the spreadsheet. I noticed it because the gridlines were missing
from the data until column J, so I did not need to use David's macro,
but it will no doubt come un useful in the future.

Thanks again for all your responses.

Glyn

(Glyn Baker) wrote in message news:<. com>...
> Hi All,
>
> I have an unusual take on an old problem. I have an Excel spreadsheet
> that is printing an extra blank page. I have seen the many many posts
> on this but all attempts to correct it have failed.
>
> My data is in the range A1:F55. When I use Ctrl End, the activecell is
> F55. I have deleted and cleared all columns G to IV and rows 56 to
> 65536. I have used VBA to reset the Used Range but still I get a
> second blank page.
>
> If I set Print Row & Column headings I can see that the second page is
> to the right of my data. It is trying to print columns H to J ie an
> extra 4 columns. If I try to set the sacling to 1 wide and blank tall,
> the data prints on one page but is compressed as it tries to squeeze
> the extra 4 columns onto the page. Obviously there is something
> happening in the last 4 columns but I cannot find it.
>
> Any ideas anyone?
>
> Regards
>
> Glyn Baker

Hi Glyn,
Thanks for the feedback, it's nice to know I was right even
after I thought I was wrong. Hope you installed the TrimALL
macro so you can use it the next time. It is one of the biggest
timesavers.

And I thank Paul for pointing out that print area is a potential
problem so included that possibility in my documentation for
last cell problems.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Glyn Baker" <> wrote ...
> Thanks for all your responses.
>
> I did not have a print area set but there were a lot of trailing
> spaces after the text in one of the cells [clipped] I noticed it because
> the gridlines were missing from the data until column J, so I did
> not need to use David's macro,
> but it will no doubt come un useful in the future.

Hi Glyn,

In addition to the worthy suggestions you already have, check the row
and columns to be repeated as titles. See Page Setup/Sheet. You
spreadsheet may have a title range that exceeds the set print area an
this will result in unwanted blank areas.

Good Luck

Pete


-
Peter
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message651345.htm