Limit number of rows and columns in OpenOffice Calc

Is there a way to limit the number of rows and columns shown in a sheet in OpenOffice Calc? I have resorted to manually hiding rows and columns outside the area that I want, but this is tedious, and is reset when using AutoFilter. Note that I'm looking for a per-sheet setting, not a per-user or per-installation setting. In Excel, there seems to be a very roundabout way to do this, but I haven't been able to find an OpenOffice equivalent: http://spreadsheets.about.com/od/excelformulas/ss/2011-05-14-excel-2010-limit-rows-tutorial.htm

asked Jul 19, 2016 at 15:50 233 2 2 silver badges 13 13 bronze badges

1 Answer 1

Of the various workarounds available, to me it seems like hiding the rows and columns is best. Here is a subroutine that will do it automatically:

Sub HideRowsAndColumns(iLastVisibleRow As Integer, iLastVisibleCol As Integer) oController = ThisComponent.CurrentController oSheet = oController.ActiveSheet document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") oCellRange = oSheet.getCellRangeByPosition(_ 0, iLastVisibleRow, 0, oSheet.Rows.Count - 1) oController.select(oCellRange) dispatcher.executeDispatch(document, ".uno:HideRow", "", 0, Array()) oCellRange = oSheet.getCellRangeByPosition(_ iLastVisibleCol, 0, oSheet.Columns.Count - 1, 0) oController.select(oCellRange) dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array()) oCellRange = oSheet.getCellRangeByPosition(0, 0, 0, 0) oController.select(oCellrange) End Sub

For example, the following routine could be called when the document is opened ( Tools -> Customize -> Events ):

Sub DoHideRowsAndCols HideRowsAndColumns(20,10) End Sub

In LibreOffice, AutoFilter did not unhide the rows. However it did in Apache OpenOffice. So for AOO, the routine would need to be called again after doing an AutoFilter .

It may be possible to add an event handler to prevent scrolling beyond certain rows. For an example of a spreadsheet event handler, see https://stackoverflow.com/questions/35240690/how-to-scroll-all-libreoffice-calc-spreadsheet-sheets-together-or-other-3d-li/35244220#35244220.

However I think that adding such a handler would be very irritating for the user. They would try to click on a cell only to have nothing happen, or perhaps the view would suddenly scroll back to the original view.