LibreOffice Calc Range Max and delete macro

Question

I have a sheet in libreoffice Calc which has an Id Column with incremental value from 1 to N.

I need to create a Macro in VBA (linked to a button i will create later) where i can select the last ID (which is the MAX id also) and delete the entire row relating to this ID.

i tried this so far

Sub suppression

dim maxId as Integer

my_range = ThisComponent.Sheets(0).getCellRangebyName("B19:B1048576")

maxId = Application.WorksheetFunction.Max(range("Dépôts!B19:B1048576"))
MsgBox maxId

End Sub

Thanks a lot for your help.

Answer 1

In libreoffice BASIC you first need to get the data array of the cell range. This is an array of arrays each representing a row of the cell range. It is indexed from zero irrespective of the location of the cell range within the sheet. Because your cell range is one column wide, each member array has only one member, which is at index zero.

As Jim K says, 'Application.WorksheetFunction' is from VBA. It is possible to use worksheet functions in LibreOffice BASIC, but these act on ordinary arrays rather than cell arrays, and the MAX function takes a one-dimensional array so it would be necessary to first reshape the data array using a loop. Furthermore, if you want to delete the row corresponding to the maximum value you are then faced with the problem of finding the index of that row using only the value itself.

It is much simpler to find the index by looping over the data array as shown in the snippet below.

Also, rather than traversing over a million rows, it would save computational effort to obtain the last used row of the spreadsheet via the BASIC function 'GetLastUsedRow(oSheet as Object)', which is supplied with LibreOffice. This is located in the 'Tools' library in 'LibreOffice Macros & Dialogs'. To use it you have to put the statement: 'Globalscope.BasicLibraries.LoadLibrary("Tools")' somewhere before you call the function.

To delete the identified row, get the XTableRows interface of the spreadsheet and call its removeByIndex() function.

The following snippet assumes that the header row of your table is in row 18 of the sheet, as suggested by your example code, which is in row 17 when numbered from zero.

Sub suppression()

' Specify the position of the index range
''''''''''''''''''''''''''''''''''''
Dim nIndexColumn As Long           '
nIndexColumn = 1                   '
                                   '
Dim nHeaderRow As Long             '
nHeaderRow = 17                    '
                                   ' 
'''''''''''''''''''''''''''''''''''' 

Dim oSheet as Object
oSheet = ThisComponent.getSheets().getByIndex(0)

' Instead of .getCellRangebyName("B19:B1048576") use: 

Globalscope.BasicLibraries.LoadLibrary("Tools")
Dim nLastUsedRow As Long
nLastUsedRow = GetLastUsedRow(oSheet)

Dim oCellRange As Object
'                                             Left           Top         Right         Bottom 
oCellRange = oSheet.getCellRangeByPosition(nIndexColumn,  nHeaderRow, nIndexColumn, nLastUsedRow)

' getDataArray() returns an array of arrays, each repressenting a row.
' It is indexed from zero, irrespective of where oCellRange is located
' in the sheet
Dim data() as Variant
data = oCellRange.getDataArray()

Dim max as Double
max = data(1)(0)

' First ID number is in row 1 (row 0 contains the header).
Dim rowOfMaxInArray As Long
rowOfMaxInArray = 1

Dim i As Long, x As Double
For i = 2 To UBound(data)
    x =  data(i)(0)
    If  x > max Then
        max = x
        rowOfMaxInArray = i
    End If
Next i

' if nHeaderRow = 0, i.e. the first row in the sheet, you could save a
' couple of lines by leaving the next statement out
Dim rowOfMaxInSheet As long
rowOfMaxInSheet = rowOfMaxInArray + nHeaderRow

oSheet.getRows().removeByIndex(rowOfMaxInSheet, 1)

End Sub