Looking for last row used programmatically

[Eryan]

Hello,

I’ve been searching for a property to show the number of rows and columns used. I want to loop through data on the sheet, but not the entire sheet itself. I also want to append data at the end of a sheet so knowing where I can safely start inserting data is important.

I’ve tried the code below but it gives me the grand total.

msgbox thiscomponent.sheets(0).rows.count

I have found some solutions but they are over 3 years old, I was hoping Libre office had moved on and maybe this is now available without moving the cursor and querying it’s position.

The excel vba equivalent would be:

UsedRange.rows.count

[komma4]

Just another example what Andrew Pitonyak’s OpenOffice Macros Explained document would answer.
Cannot believe that’s not found on Google’s first page results and everybody wishing to program with Basic would go, get and read.

[Eryan]

Thanks for that. I had found that before, but wasn’t sure about using interfaces or if .EndRow was a property of the sheet, and wasn’t having much luck. But it is a great resource.

[komma4]

To find properties or methods download the SDK (=documentation), which will list all of them, for every document type and all objects.
Edit: even more better: MRI extension, which will display object’s under inspection and can link to API documentation of SDK.

[Andrew_P]

The MRI extension doesn’t appear to work and isn’t even installable in LibreOffice 4.3. When I tried to install it, the Extension Manager displayed a message warning of a syntax error in MRI.py, line 21. The “MRI - UNO Object Inspection Tool” extension page says MRI version 1.1.2 was released July 17, 2013 and tested with LibreOffice 3.3, LibreOffice 3.4, and LibreOffice 3.5.

[JohnSUN]

Here this is not much more complicated

Function UsedRange(oSheet As Variant) As Variant
Dim oCursor As Variant
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(False)
	oCursor.gotoStartOfUsedArea(True)
	UsedRange = oCursor
End Function

With this range you can get

Function RowsCount(oRange As Variant) As Long 
	RowsCount = oRange.getRows().getCount()
End Function

Function LastRow(oRange As Variant) As Long 
	LastRow = oRange.getRangeAddress().EndRow
End Function

Example of usage

Sub test
Dim oRange As Variant, sMessage As String 
	oRange = UsedRange(ThisComponent.getCurrentController().getActiveSheet())
	sMessage = "Used Range of Active sheet is " & oRange.AbsoluteName & Chr(10) & _
		"It has " & RowsCount(oRange) & " rows" & Chr(10) & _
		"First empty row is " & (LastRow(oRange) + 2)
	MsgBox sMessage
End Sub

[Eryan]

Thank you. That works.