| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
It's a very nice technique.
Storm wrote: Dave! You're awesome! Oh, yes, my syntax did not have the space in between the quotes. By the way, the reason I need this as automated as possible is because I'm sending the report to non-Excel familiar people. So I'm trying to make it as fool-proof as possible and without them having to do anything extra as possible. So anything that will need them to do anything everytime, is out-- so options 1 to 3 are probably out. But option 4 is awesome! Thank you very much!!! "Dave Peterson" wrote: First, just to make life easier, I hope you're actually returning "" (no space included--not " "). Second, you have a couple of choices. #1. Apply Data|Filter|autofilter to that column. Show the non-blanks Print that and then Data|Filter|Show all (to see everything again) But it sounds like the worksheet is protected. If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm #2. Manually change the print range via: select the range to print file|print area|set print area #3. Just print what you select Select the range to print file|print choose Selection #4. Have excel adjust the print range for you: I'm gonna use column C as a column that always has something in it if that row should print. You can change that if your formula is in a different column. Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$C$1:$C$2000<""),ROW(Sheet1!$C$1:$C$2000) ) (Make that 2000 big enough to extend past the last possible row--but don't use the whole column.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5) That last 5 represents the last column to print (A:E). Change it to what you want. And change the worksheet (sheet1) if necessary (in all the places). Storm wrote: Hello. My question is, let's say I have columns A-B unprotected, so users can drop the current month's report. Column C is protected and has an if formula to check if A is empty, " ", else to get B less A. The report that will be dropped every month on Columns A-B may be longer (in rows) or shorter than the previous month. So I copy-pasted the formula in Column C down to C200, which is just an estimate. With this template, I did a print and it's actually printing up to cell C200, I'm assuming because there is a formula in there. What I am hoping to do is to print only the rows with visible data. So if Col A-B is only up to row 50, just to print up to that row. Without having to select a print area everytime, is there a way to automate the process that when I am printing the report only the visible data will be printed? thank you. -- Dave Peterson -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| how to split data from 1 row into two rows continuously | Excel Worksheet Functions | |||
| Loading Column Data with blank Rows into Data Validation Box | Excel Worksheet Functions | |||
| tried using SUBTOTAL function (XL XP) and it doesn't seem to work | New Users to Excel | |||
| Unhiding rows using data validation | Excel Worksheet Functions | |||
Threaded Mode
