Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tonyoc
 
Posts: n/a
Default In Exel 2000, stop the blank cells (with formulas) from printing.

Exel 2000. I have entered formulas into 398 rows of a spreadsheet . The
fromulas are written so that the cell remain blank until a value is entered
into the referenced cells. When printing, the cells with formulas, even
though they are blank, print resulting in several blank pages. Other then
"select print", is there a way to only print the cells that have values?
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

I like to apply Data|filter|Autofilter.

Then I filter to only show the non-blanks and then data|filter|showall after I
print.

or....
(saved from a previous post)

(from a previous post)

Select your worksheet (I'll call mine Sheet1.)

Pick a column that will always have data if that row has anything showing. I'm
gonna use column B.

Then Insert|Name|Define
In the Names in Workbook Box, type: LastRow
In the refers to box, type this formula:
=MAX(ROW(sheet1!$B$1:$B$500)*(sheet1!$B$1:$B$500< ""))
adjust the rows (500 to what you want: 8000???).

Now create another name for whole thing if all the rows had stuff that showed.
Call it FullPrint
In my example, it looked like:
=sheet1!$A$1:$H$500

Now one more range name, but this one is special. It's a worksheet range name.
The names box should look like this:
sheet1!PrintArea
the refers to box should be:
=offset(fullprint,0,0,lastrow)

You need the sheetname in front of PrintArea (with the exclamation point!!).

The trick with the LastRow name is that it uses an array formula (but you don't
hit ctrl-shift-enter) to find the last "non-blank" (different than non-empty)
cell in that range (b1:b500).

The PrintArea is a name that excel uses to define the printRange.

The offset() stuff says to take the fullprint range, starting at the topleftcell
of fullprint (that's the 0,0 portion) and resize it to the number of rows
calculated by LastRow.

=======
this is a pretty neat technique. I found it (well close to it) in a template
that MS gives away.

It's used for loan repayment calculations:

Here's a loonnnnnnnnng link to it (all one line in your browser):

http://officeupdate.microsoft.com/Te...&cid=0.138.139



tonyoc wrote:

Exel 2000. I have entered formulas into 398 rows of a spreadsheet . The
fromulas are written so that the cell remain blank until a value is entered
into the referenced cells. When printing, the cells with formulas, even
though they are blank, print resulting in several blank pages. Other then
"select print", is there a way to only print the cells that have values?


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Skip blank cells in diagrams hlp Charts and Charting in Excel 9 February 24th 06 03:32 PM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 02:15 AM
how to skip the blank cells nayeemoddin Excel Discussion (Misc queries) 1 December 6th 04 08:07 AM
copy blank cells Vicneswari Murugan Excel Discussion (Misc queries) 1 December 1st 04 03:12 PM
copy blank cells Vicneswari Murugan Excel Discussion (Misc queries) 0 December 1st 04 04:33 AM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"