How to loop on range of rows in LibreOffice Calc spreadsheet, comparing cell values, setting cell values and deleting row, if condition is true

Question

I have the following requirement in my LibreOffice Calc spreadsheet:

ForEach Row 'r' in selected range, starting from the last row in the range, and moving backwards (up) one row at a time,

do some cell value comparisons, and based on that, either skip the row, or set some cell values, and delete the selected row, then proceed with the same process, with the row just above that.

ie.,

Representing CellValue(Column[A], Row[r]) as A[r],

And Representing the row before (just above) that, as A[r-1],

I need to do the following:

  FOR (r = LastRowInSelectedRange; r>1; r=r-1) {

    IF FollowingConditionsAreTrue (
      (r > 1)
      AND (A[r] IsEqualTo A[r-1])
      AND (B[r] IsEqualTo C[r-1])
      AND (E[r] IsEqualTo E[r-1])
    ) ThenDoTheFollowing {

      SET C[r-1] = C[r]
      DeleteRow(r)

    } EndIF

  } EndFOR

Question: How can we implement this in LibreOffice Calc?

score 2 · Answer 1 · answered Jul 24 '20 at 15:04

The following should do the trick. It assumes your data are in the range A1:E10 in a sheet called Sheet1. To use a different range change the appropriate lines. If you want to select the range by hand, comment out the line oCellRange = oSheet.getCellRangeByName("A1:E10") and uncomment the line oCellRange = oDoc.getCurrentSelection(). That will only work if you have a single selection, not multiple ones.

Sub iterateThroughRange()

    Dim oDoc As Object
    oDoc =  ThisComponent

    Dim oSheet As Object
    oSheet = oDoc.getSheets().getByName("Sheet1")

    Dim oCellRange As Object    
    oCellRange = oSheet.getCellRangeByName("A1:E10") 

    'The above line gets a named range. To get a slected area instead
    'comment it out and uncomment the following line:
    'oCellRange = oDoc.getCurrentSelection() 

    Dim oTableRows As Object
    oTableRows = oCellRange.getRows()

    Dim nRows As Integer
    nRows = oTableRows.getCount()

    Dim oTableColumns As Object
    oTableColumns = oCellRange.getColumns()

    Dim nColumns As Integer
    nColumns = oTableColumns.getCount() 

    Dim oThisRow As Object, oPreviousRow As Object
    Dim oThisRowData() As Variant, oPreviousRowData() As Variant
    Dim oThisRowAddress As Variant

    For r = nRows - 1 To 1 Step - 1

        oThisRow = oCellRange.getCellRangeByPosition(0, r, nColumns - 1, r)
        oThisRowData = oThisRow.getDataArray()         

        oPreviousRow = oCellRange.getCellRangeByPosition(0, r - 1, nColumns - 1, r - 1)
        oPreviousRowData = oPreviousRow.getDataArray()

        'Column A = index 0
        'Column B = index 1
        'Column C = index 2
        'Column E = index 4

        If oThisRowData(0)(0) = oPreviousRowData(0)(0) AND _
           oThisRowData(0)(1) = oPreviousRowData(0)(2) AND _
           oThisRowData(0)(4) = oPreviousRowData(0)(4) Then

            oPreviousRowData(0)(2) = oThisRowData(0)(2)
            oPreviousRow.setDataArray(oPreviousRowData)

            'The following two lines delete the range and move the cells up
            oThisRowAddress = oThisRow.getRangeAddress()
            oSheet.removeRange(oThisRowAddress, com.sun.star.sheet.CellDeleteMode.UP)

            'To delete the entire row instead of just the affected cells,
            'comment out the above two lines and uncomment the following line:
            'oTableRows.removeByIndex(r, 1)

        End If

    Next r

End Sub

How to loop on range of rows in LibreOffice Calc spreadsheet, comparing cell values, setting cell values and deleting row, if condition is true

1 Answers1