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.
Enjoy !
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