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/T...31,137,&RC=7&M=11&mh=20&qu=&ct=&cid=0.138.139