Printing only what can be seen

A

abxy

Ok, I have a worksheet or template rather with about 200 rows (each row
size 14 font), each row (after the 7th row) has the same conditional
formats and formulas in the same columns. The conditional formats and
formulas are done such that every cell is a blank cell until data is
entered into a cell on that row.

Now, each day I paste in data, but it's not everyday that all 200 rows
are needed. However, Excel still wants to print each and every last row
that I have those conditional formats and formulas on, despite the fact
that they have no visible data in them. Excel just prints up extra
blank pages. I wouldn't mind, except that I have footers and rows
repeated at the top on my page setup. So I think you can understand my
fustration when I print up wasted paper that have my repeated rows at
the top and page of 1 of # at the bottom.

Is there any way that Excel will only print my worksheet up to the last
row with data that can actually be seen?
 
D

Dave Peterson

You could change the print range before you print.

File|Print Area|set print area

You could select your range to print and then just print the selection.
File|Print (at the bottom left of the dialog in the "print what" portion

And my favorite if the cells are really blank.

apply Data|Filter|autofilter
filter on non-blanks

and print the visible cells

then Data|Filter|showall
to see everything.
 
A

abxy

ok, I know how to use set print area, but is there anyway that this ca
automatically be done so that I won't have to first select the rang
then go to set print area everytime
 
D

David McRitchie

With a macro

Sub Fix_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub

Sub Fix_lastcell()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim x As Long 'Attempt to fix the lastcell on the current worksheet
x = ActiveWorksheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
End Sub

The above are the simplest/safest to use, they do not always work.
You can verify results with Ctrl+End, If you need more drastic
action you can identify the cell you want to become the last cell
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelast
Consequences could be loss of hidden formulas in out of the
portions of the spreadsheet.

If no familiar with using macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Highly recommend that you use your real name so you and others
can find your postings since you are posting to the Excel newsgroups.
I am referring to Google Usenet Archives
http://www.mvps.org/dmcritchie/excel/xlnews.htm
where all questions/responses in the actual newsgroups can be found.
 
Top