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.