Libreoffice calc: function that deletes rows

Question

I am new to Libreoffice Calc.
My if/else function populates cells in a new sheet.
The function generates a lot of empty rows in the new sheet.
Is it possible to delete those empty rows with a function?
Something like:

=IF(NOT(ISEMPTY(sheet1.A1));sheet2.A1; delete row ???)

Or should this be done with a macro?
Any help that gets me started is very welcome.

Answer 1

I am not sure how Standard Filter works, but I just wrote this macro code that should do the trick. Assumptions:

1. Works on current active sheet
2. Checks only first column for blank content or blank string
3. After more than 100 blank rows in a row, the code will stop. If you think there could be more, you could set cMaxBlankRows to a higher number, but the code will take longer to run if you set it too high.

const cMaxBlankRows = 100 ' stop processing if there are more than this number of blanks in a row
const cMaxRows = 1048575 ' LibreOffice max number of rows - 1, it is zero based
Sub subDeleteBlankRows()
    oDoc = ThisComponent
    oSheet1 = oDoc.getCurrentController().getActiveSheet()
    iIdx = 0
    iConBlank = 0    ' number of continuous blanks
    Do While iIdx <= cMaxRows 
        oCell = oSheet1.getCellByPosition(0,iIdx)
        If oCell.getType() = com.sun.star.table.CellContentType.EMPTY OR Len(oCell.getString()) = 0 Then
            ' found an empty cell, delete the row
            oSheet1.Rows.removeByIndex(iIdx,1)
            iConBlank = iConBlank + 1
            ' don't advance iIdx
            if iConBlank > cMaxBlankRows Then Exit Do
        Else
            iIdx = iIdx + 1
            iConBlank = 0
        End If
    Loop
End Sub