Ask Your Question

looking for last row used programmatically [closed]

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

Eryan gravatar image

updated 2020-07-21 01:31:57 +0100

Alex Kemp gravatar image


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:

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 01:32:07.944586


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 +0100 )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 +0100 )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 +0100 )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, 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 +0100 )edit

1 Answer

Sort by » oldest newest most voted

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

JohnSUN gravatar image

Here this is not much more complicated

Function UsedRange(oSheet As Variant) As Variant
Dim oCursor As Variant
    oCursor = oSheet.createCursor()
    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


Thank you. That works. :)

Eryan gravatar imageEryan ( 2017-06-28 13:44:50 +0100 )edit

Question Tools



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

Seen: 4,263 times

Last updated: Jun 27 '17