Following a very rich question on this thread, I found myself having to know the number of the last line occupied by a non-empty cell in a given column. If the thing is very simple to do with VBA (for example [Cells.Rows.Count, "C"]), it is a little bit more complicated to do with the API. But it is thanks to the intervention and the propositions of mikele that it is possible to design a function knowLastCell() which operates a search of not empty cells, on a column or a given line (one specifies a type for that). The rangeAddresses property of the queryEmptyCells's object contains one or more arrays. We always take the last one. And we extract, depending on the type of search that we do, the startRow or startColumn property, minus one, that the function returns.
Code: Select all
Option explicit Sub Main ' Search the number of the last line with something in cell in the column number 2. msgBox knowLastCell("Sheet1", 2, 0) ' Search the number of the last column with something in cell in the line number 12. msgBox knowLastCell("Sheet1", 12, 1) End Sub ' If myType = 0, the search is do on the column. ' If myType = 1, the search is do on the line. Function knowLastCell(mySheetName As String, myColumnOrMyLine As Long, myType As Integer) As Long Dim mySheet As Object, myBlock As Object, myFound As Object On Error Goto myError mySheet = thisComponent.sheets.getByName(mySheetName) Select Case myType Case 0 : myBlock = mySheet.columns(myColumnOrMyLine) Case 1 : myBlock = mySheet.rows (myColumnOrMyLine) End Select myFound = myBlock.queryEmptyCells.rangeAddresses With myFound(uBound(myFound)) Select Case myType Case 0 : knowLastCell = .startRow - 1 Case 1 : knowLastCell = .startColumn - 1 End Select End With Exit Function myError: ' If the column or the line is empty. knowLastCell = -1 End Function