OO-Snippets: Hide/Show Rows
Commons
| Keywords | hide, show, showing, hiding, cell, cells, cell dependant, blank, empty |
|---|---|
| Language | OOBasic |
| Application | Calc |
| Authors | Ian Laurenson (initial)
René Zimmer |
| Supported Versions | |
| Supported OS | |
| Question |
How do I hide/show rows depending on cell content?
The problem was I wanted to hide rows if they are empty. I have a template spreadsheet and the number of filled rows varies in the final document. To have a nice printout and a nice screenview the empty rows should be hidden automa- tically. |
| Answer | The solution goes as follows: Name a column of cells by marking them and then "Insert/Name/Define...". Those cells are tested on a criterium, e.g. if empty. Run the macro snippet on the spreadsheet. You may assign the macro to an event, e.g. "Document saved", by "Extras/Macros/Macro.../Assign...". |
Code-Snippet-Listing (snippet-source)
Sub HideBlankRows oDoc = thisComponent REM Define a range of cells to be tested oRangea = oDoc.namedRanges.getbyName("Ida").ReferredCells oRanges = oDoc.namedRanges.getbyName("Ids").ReferredCells REM Loop to test first range, ie. Ida for i = 0 to oRangea.rows.count -1 oCell = oRangea.getCellByPosition(0, i) REM test criterium, here if empty if oCell.string = "" then REM Hide the row containing the tested cell if test condition is true oCell.rows.isVisible = false REM otherwise show the row containing the tested cell REM This is to avoid the ShowAllRows macro if already REM hidden cells are filled else oCell.rows.isVisible = true end if next REM Loop to test second range of cells,ie. Ids for i = 0 to oRanges.rows.count -1 oCell = oRanges.getCellByPosition(0, i) if oCell.string = "" then oCell.rows.isVisible = false else oCell.rows.isVisible = true end if next REM End of Loop End Sub Sub ShowAllRows oDoc = thisComponent REM Define a range of cells and show them unconditionally oRangea = oDoc.namedRanges.getbyName("Ida").ReferredCells oRangea.rows.isVisible = true REM Define a second range ... oRanges = oDoc.namedRanges.getbyName("Ids").ReferredCells oRanges.rows.isVisible = true End Sub |
Changelog
| Date | User | Modification |
|---|---|---|
| 2004-08-16 | zimmer | Summerized as codesnippet |
