[Solved] Use Macro to toggle row visibility

[deeaar]

I actually had this working but Calc deleted all my macros and now I can't find / recall how I managed it:

spread1 contains some stocktake data
spread2 has IF statements in the cells to show labels(text) and data(numbers) IF the stock items are present in spread1

So spread2 is often mostly (appears) empty if I don't need to order stock.

For ease of reading/faxing (yes, faxing!) I "Hide" the Rows that have null in a specific cell
ie. they appear empty but that's the IF <no data>, ""

Code: Select all

=IF($Stocktake_Calculator.N16="","",$Stocktake_Calculator.N16)

I had two buttons before HideEmpty and ShowAll, (maybe this one I can do again without too much fuss) but I can't get the HideAll if cell empty working.
Remembering that the cell isn't actually empty, just a formula printing/resulting in "" (nullstring)

This is my current attempt at a toggle macro

Code: Select all

oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Stocktake_Calculator")
oRows= oSheet1.getRows()
for i = 7 to 13 '1st row has index 0 so add 1 to get sheet actual
   oRow = oRows.getByIndex(i)
   oCell = oSheet1.getCellByPosition(i,2)
   If oCell.String = "x" Then
      oRow.isVisible = True
   Else
      oRow.isVisible = False
   End If
next i

[deeaar]

OK, stupid me had the Col, Row references backwards, <sigh>

oCell = oSheet1.getCellByPosition(i,2)
changed to
oCell = oSheet1.getCellByPosition(2,i)

[karolus]

please update your Signatur … AOO is somewhere near 4.1 and Libreoffice should be at least >= 6.1

[Lupp]

please update your signature … AOO is somewhere near 4.1 and LibreOffice should be at least >= 6.1

The often very useful function REGEX() only is implemented in LibO V 6.2 or higher.

[karolus]

@Lupp
I know REGEX … but in which Context is your Comment related to this topic ??