How to control how much gets printed (prevent printing blank rows)

[Andreas Hansson]

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

 

[Andreas Hansson]

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

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(A1>0,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 ([email protected], 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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Jialiang Ge [MSFT]]

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 ([email protected], 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:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================