Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to control how much gets printed (prevent printing blank rows)
Hello!
I have an Excel spreadsheet that is supposed to be filled in by customers. I have some columns that will take input and other columns that I have prefilled with formulas. Now, my problem is that when I go to print this document, Excel prints every row that has formulas on it, even if the formulas generate an empty result when there are no customer-input values on that row. I have prefilled the spreadsheet so the customer can input a few hundred rows of data if they would have that much data, and this comes out as several empty (only headers) pages getting printed. Is there a solution to this problem? I would prefer if it could be somehow done with a setting on the formula or lines or cell formatting, so the customers don't get the Excel macro warning when they open the spreadsheet, if it can be avoided. Thanks in advance for any help, Andreas Hansson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to control how much gets printed (prevent printing blank rows)
Hello again! :)
The formulas that generate the "empty" results follow this pattern: =OM(indata!$A51=0;;indata!D51) which in english would presumably be =IF(indata!$A51=0,,indata!D51) It sounds likely that that nothing is the same as an empty string. Workaround 1 looks prettiest, but because of the way the spreadsheet is set up with multiple tabs, and fetching data from the input data tab to another it seems difficult to do. So I am going with workaround 2 for now and they will just have to accept the macro warning. I wrote some code like the following to do it: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ix As Long ix = 6 Do While Val(Blad1.Cells(ix + 1, 1).Value) 0 ix = ix + 1 Loop Blad1.PageSetup.PrintArea = "$A$1:$I$" & ix End Sub Thanks for your help! Andreas Hansson ""Jialiang Ge [MSFT]"" skrev i meddelandet ... Good afternoon Mr. Hansson. Nice to see you again. You mentioned that empty result is generated for prefilled rows. Am I right that this "empty" result actually stands for ("")? For example: =IF(A10,A1,"") According to the KB http://support.microsoft.com/kb/214103/en-us, ("") is not considered as a blank cell by Excel. Excel still thinks of it as a non-blank text cell with the value "". When we prints an Excel worksheet, Excel prints the used range which would includes those non-blank text cells and thus results in the problem: several empty (only headers) pages getting printed. As far as I know, Excel does not have a setting that can allow a formula to output a real blank value (I will confirm this point with the product group), however, I figure out two possible workarounds for your reference: ================== Possible workaround 1. Instead of pre-filling a large range of cells with the formula, we can format a small range of cells (1 line of title and 1 line of data with formula and space for client's input) as a "Table". When you clients want to input to the next row, the new row will be appended to the table, and the cells will be set with the formula automatically. This avoids pre-filling a large range, and also avoids the problem: several empty (only headers) pages getting printed. I attach a demo xlsx to this message. You can download it with Outlook Express or Windows Mail, and see how it works. ================== Possible workaround 2. As you may have already considered, a VBA macro can also help us avoid the problem. But the macro results in a macro warning when the client open the spreadsheet. The basic logic of the macro is: 1. iterate the cells in the sheet and determine the non-empty range that we are going to print. 2. select the range (range.Select) 3. print the range (Selection.PrintOut) Please let me know if you want a sample for the above macro logic. Have a very nice day! Best Regards, Jialiang Ge , remove 'online.') Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/en-us/subs...#notifications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://support.microsoft.com/select/...tance&ln=en-us. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to control how much gets printed (prevent printing blank rows)
You are welcome!
I will close your post "Problem scrolling owner-drawn listbox" and this one temporarily on my side. But if you have any follow-up questions/concerns, please feel free to post them here. I will take action to support you in no time. Have a nice weekend! Regards, Jialiang Ge , remove 'online.') Microsoft Online Community Support ================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . This posting is provided "AS IS" with no warranties, and confers no rights. ================================================= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I prevent file from being saved or printed in Excel? | Setting up and Configuration of Excel | |||
How to prevent extra blank pages from printing? | Excel Discussion (Misc queries) | |||
prevent empty rows from printing | Excel Worksheet Functions | |||
How to prevent a specific worksheet from being printed ! | Excel Programming | |||
How to Avoid Printing Blank Rows | Excel Programming |