Apache OpenOffice (AOO) Bugzilla – Issue 54039
add NOPRINTROW and NOPRINTCOL functions
Last modified: 2013-08-07 15:12:27 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 rows. 6) Print the sheet. 7) Return to the "TempView" view (to undo all the things hidden in this process). 8) Delete the "TempView" (so it doesn't interfere with future use and other sheets) 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 ActiveSheet.Unprotect '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 ActiveWorkbook.Names("Print_Area").Delete 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 ActiveWorkbook.CustomViews("TempView").Show ActiveWorkbook.CustomViews("TempView").Delete ActiveSheet.Protect Exit Sub ErrorMsg: 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 Sheets(i).Unprotect Next i 'call Print_Valid() procedure to setup & print the sheet Print_Valid 'protect all sheets in the workbook For i = 1 To Sheets.Count Sheets(i).Protect Next i Exit Sub ErrorMsg: 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. ("Sheetname!Rangename") 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 written. Sincerely, Tim Deaton timdeaton@twave.net (home email) P.S. I first sent this message to discuss@openoffice.org 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.
So this is one for the requirements team
OpenOffice.org 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: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ 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: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
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.)