Limit number of rows and columns in OpenOffice Calc

Question

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

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.

Another alternative is that instead of hiding the cells, protect them to prevent editing. See https://ask.libreoffice.org/en/question/17106/is-it-possible-to-impose-a-limit-columns-and-rows-on-spreadsheet-size/.

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.