Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
Your data should automatically only print visible data....do you have
formatting in the cells that are empty that are printing? ie...are your cells in color or have an outline? if they do, they will print regardless of data...so, just unformat them and you shoud be good to go. Hopefully helpful, Carla Wood "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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
Hi Carla, thanks for the response. Nope, there is not formatting in the
cell. If you try like just keying anything on cell b5. then scroll down to c200 and just type in the formula of "=if(isblank(a200),"",b200-a200)". since there's no data in a200, c200 will bring back a value of " ". then try print preview, it'll give you more than one page to print, which I'm guessing is because you've got a formula on C200. Any help? "CarlaWood" wrote: Your data should automatically only print visible data....do you have formatting in the cells that are empty that are printing? ie...are your cells in color or have an outline? if they do, they will print regardless of data...so, just unformat them and you shoud be good to go. Hopefully helpful, Carla Wood "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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
Well maybe....it page 2 on the print preview blank or does it have something
on it? How many pages is the print preview? "Storm" wrote: Hi Carla, thanks for the response. Nope, there is not formatting in the cell. If you try like just keying anything on cell b5. then scroll down to c200 and just type in the formula of "=if(isblank(a200),"",b200-a200)". since there's no data in a200, c200 will bring back a value of " ". then try print preview, it'll give you more than one page to print, which I'm guessing is because you've got a formula on C200. Any help? "CarlaWood" wrote: Your data should automatically only print visible data....do you have formatting in the cells that are empty that are printing? ie...are your cells in color or have an outline? if they do, they will print regardless of data...so, just unformat them and you shoud be good to go. Hopefully helpful, Carla Wood "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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
Based on page margins of (in inches): Top & Bottom at 1 and left and right at
..75, there are 4 pages in print preview. Pages 2 to 4 are blank, because there is no visible data but that one formula in cell c200. Without having to set print area everytime, how can I tell Excel to just print based on rows with visible data and not cells that are non blank because they have formula in them? thanks again "CarlaWood" wrote: Well maybe....it page 2 on the print preview blank or does it have something on it? How many pages is the print preview? "Storm" wrote: Hi Carla, thanks for the response. Nope, there is not formatting in the cell. If you try like just keying anything on cell b5. then scroll down to c200 and just type in the formula of "=if(isblank(a200),"",b200-a200)". since there's no data in a200, c200 will bring back a value of " ". then try print preview, it'll give you more than one page to print, which I'm guessing is because you've got a formula on C200. Any help? "CarlaWood" wrote: Your data should automatically only print visible data....do you have formatting in the cells that are empty that are printing? ie...are your cells in color or have an outline? if they do, they will print regardless of data...so, just unformat them and you shoud be good to go. Hopefully helpful, Carla Wood "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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing only Rows with Visible Data
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 |
#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 |
Reply |
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 |