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

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]"" <(E-Mail Removed)> skrev i meddelandet
news:(E-Mail Removed)...
> 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 ((E-Mail Removed), 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 Removed).
>
> ==================================================
> 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.

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 ((E-Mail Removed), 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 Removed).

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