Ask Your Question
1

looking for last row used programmatically

asked 2017-06-27 17:08:54 +0200

Eryan gravatar image

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
edit retag flag offensive close merge delete

Comments

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.

komma4 gravatar imagekomma4 ( 2017-06-27 20:07:59 +0200 )edit

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.

Eryan gravatar imageEryan ( 2017-06-28 13:41:31 +0200 )edit

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.

komma4 gravatar imagekomma4 ( 2017-06-28 20:21:13 +0200 )edit

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.

Andrew_P gravatar imageAndrew_P ( 2018-03-15 07:27:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2017-06-27 18:34:01 +0200

JohnSUN gravatar image

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
edit flag offensive delete link more

Comments

Thank you. That works. :)

Eryan gravatar imageEryan ( 2017-06-28 13:44:50 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-06-27 17:08:54 +0200

Seen: 1,533 times

Last updated: Jun 27 '17