Help setting a print range in a data sheet
 

I'd apply data|filter|autofilter to that set of rows and then show only the
non-blanks.

If that's considered fiddling....

(from a previous post)

Non-VBA method.

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




mathew wrote:

I have a spreadsheet that requires imported information from another program.
This part works great! The sheet has a calculation range from A11 to M 3010,
with headings being in A1 to M10. All of the rows in this data range have
formulas in them because there is no telling how many rows will have data in
them. Below this data range are more calculations, that I do not want the
user to play with, nor I can I move them. I also do not want the users, some
of whom are not capable, to set the print range, as that has caused several
issues. Any ideas? I'm really stuck!

--

Dave Peterson