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.

It will be much easier to get the desired result not with formulas, but with a filter. Look at the ability of the Standard Filter to select rows by condition (is that what you do with your if/else function?) and save the filtering result in a new location. — JohnSUN, Nov 01 '21 at 10:25

score 0 · Answer 1 · edited Jun 01 '22 at 09:31

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

Works on current active sheet
Checks only first column for blank content or blank string
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

Libreoffice calc: function that deletes rows

1 Answers1