Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help setting a print range in a data sheet | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting Print range | Excel Programming |