Issue 54039

Summary: add NOPRINTROW and NOPRINTCOL functions
Product: Calc Reporter: timdeaton <tim>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOO 2.0 Beta2   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---

Description timdeaton 2005-08-31 15:37:53 UTC
I would like to urge you to add a NOPRINTROW and a NOPRINTCOL function to 
OpenOffice.  I'll explain what I mean below, and include the details of what I 
do now (in hopes that it helps).  I've never seen what I do in print, but 
because of how frequently I use it, I'd be very surprised if many others 
didn't find such functions to be almost indispensible once they found them.
The first spreadsheet program I used (ancient shareware: ExpressCalc) had a 
function called NOPRINT.  I could put it in a cell by itself, or use it inside 
another function (like IF(G15=0,NOPRINT,"")).  Either way, if any cell in a 
given row evaluated to "NOPRINT", then that row would not be printed when I 
printed the spreadsheet.
This function was very handy whenever I wanted to build a re-usable table that 
might allow for 100 rows of data entry & related calculations, but in which 
not all rows are likely to be filled every time.  For instance, I might have 
an invoice sheet listing 30 different possible items, but I only want the 5 
that happen to have quantities filled in to actually show up on the printout.
Unfortunately, I've never found a similar function in any other spreadsheet 
program.  However, in Excel97, I can achieve the same result using a couple of 
macros, some predefined range names, and a "Print" button that runs the 
macros.  I define a convenient column with the range name "NOPRINT", the cell 
that I want to appear (after printing) at the upper-left corner of the screen 
as "PRESTART", and the cell that I want to be active when printing is finished 
as "START".  If there happen to be any columns I don't want printed, I define 
them in a fourth range name called "COLNOPRINT".  Finally, in the NOPRINT 
column, I put the NA() function on each row that I never want printed, and I 
use an IF() statement that evaluates to NA() unless the row should be printed.
Then, the macros basically do this:
1) Unprotect the sheet.
2) Go to the PRESTART and START cells to get to the view I want to return to.
3) Create a "TempView" view for later use.
4) Hide any columns included in a COLNOPRINT range.
5) Find all cells in the NOPRINT column that evaluate to NA(), and hide those 
6) Print the sheet.
7) Return to the "TempView" view (to undo all the things hidden in this 
8) Delete the "TempView" (so it doesn't interfere with future use and other 
9) Protect the sheet.
The macros are as follows:
' FindRange Function
' Macro from MS Excel Tech support 5/1/96 by Paul Peck
' to find if a range exists
Function FindRange(r)
    Dim x As Range
    On Error Resume Next
    Set x = ActiveSheet.Range(r)
    On Error GoTo 0
    If Not (x Is Nothing) Then FindRange = True
End Function
' Print_Valid Macro
' Macro recorded & edited 11/24/99 by Tim Deaton
Sub Print_Valid()
    On Error GoTo ErrorMsg
    'set up & create the view to return to when finished
    If FindRange("PRESTART") Then
        Application.Goto Reference:="PRESTART", Scroll:=True
    End If
    If FindRange("START") Then
        Application.Goto Reference:="START"
    End If
    ActiveWorkbook.CustomViews.Add "TempView", True, True
    'Hide any columns that should not be printed
    If FindRange("COLNOPRINT") Then
        Application.Goto Reference:="COLNOPRINT"
        Selection.EntireColumn.Hidden = True
    End If
    'select "noprint" range, then "n/a" cells, then hide "n/a" rows
    Application.Goto Reference:="NOPRINT"
    Selection.SpecialCells(xlFormulas, 16).Select
    Selection.RowHeight = 0
    'delete any defined print range so the entire visible area will print
    If FindRange("Print_Area") Then
    End If
    'view, then print the sheet
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
    'return to the view you started with, then delete that view name
    Exit Sub
    Msg = Str(Err) & ": " & Error(Err)
    MsgBox Msg, vbCritical
    Resume Next
End Sub
' Print_Valid_Plus Macro
' Macro recorded & edited 12/15/99 by Tim Deaton
Sub Print_Valid_Plus()
    On Error GoTo ErrorMsg
    'unprotect all sheets in workbook
    For i = 1 To Sheets.Count
    Next i
    'call Print_Valid() procedure to setup & print the sheet
    'protect all sheets in the workbook
    For i = 1 To Sheets.Count
    Next i
    Exit Sub
    Msg = Str(Err) & ": " & Error(Err)
    MsgBox Msg, vbCritical
    Resume Next
End Sub

Usually when I need a NOPRINT function, I also need to protect the sheet to 
protect various pre-defined formulas.  But the macros use VIEWs, they have 
to "Unprotect" all sheets in the workbook and then "Protect" them at the end 
of the process.  Therefore, if I have multiple sheets in the workbook, the 
PRINT button has to run the "PrintValidPlus" macro.  Otherwise, I have the 
button run the "PrintValid" macro.
In order for this to work well, I have to define the range names as local ONLY 
to this sheet.
Otherwise, I could only use this technique on one sheet in a workbook.
It would be very helpful if I could do the same with views, but Excel doesn't 
offer that option.  Starting with Excel97, all views are global to the entire 
workbook -- a very bad idea in my opinion.
I hope I've explained this in enough detail and yet clearly enough that some 
of your spreadsheet developers will agree that it's needed, and will build it 
into the spreadsheet program.
In any case, thanks for taking the time to read and understand what I've 
Tim Deaton (home email)
P.S.  I first sent this message to in May 2004.  I 
think that went into a forum somewhere, but I can't find it, so I don't know 
what happened to it.
Comment 1 frank 2005-09-22 10:27:45 UTC
So this is one for the requirements team
Comment 2 ace_dent 2008-05-16 01:41:42 UTC Issue Tracker - Feedback Request.

The Issue you raised is currently assigned to 'Requirements' pending review, but
has not been updated within the last 2+ years. Please consider re-testing with
one of the latest versions of OOo, as the problem(s) may have already been
addressed. Either use the recent stable version:
or consider trying the new OOo 3 BETA (still in testing):
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
Comment 3 timdeaton 2008-05-18 04:55:22 UTC
Please do not close this out.  And please DO try to find someone to work on it.
 A "NOPRINT" function (especially for rows) really is a very handy tool to have
whenever you have many rows of data but only want a few of them to print out. 
(For instance, you have a list of 100 products, but only want those with a
quantity greater than zero to print.)