Get Used Range of Sheet

[Vitus_Hawkridge]

Hello community,

I have to frequently select the used range of a sheet that stores a big amount of data. I am aware of several methods but not really skilled enough to use them correctly I think. In pseudo code I want to do the following:

oRange = Thiscomponent.getusedrange()

I have tried

oDoc = Thiscomponent
oSheet = oDoc.sheets.getbyname("Sheet1)
oRange = oSheet.RangeAddress.EndRow. ← this is awefully slow it always goes down to the very bottom (1048576 rows).

Another possebility I read in this forum is the ctrl curser down method, but that seems overly complicated and error prone (in case empty spaces in table)

Thanks a lot for the help in advance.

Cheers

[robleyd]

Ctrl + Home will move to cell A1 in the active sheet; Ctrl + End will move to the last non blank cell on the active sheet.

[erAck]

Create a cursor that spans the used area, like this:

sheet = ThisComponent.Sheets.getByName("Sheet1")
cursor = sheet.createCursor()
cursor.gotoStartOfUsedArea(False)   ' move to start of used area
cursor.gotoEndOfUsedArea(True)      ' expand to end of used area
' cursor.RangeAddress is the spanned cell range
lastrow = cursor.RangeAddress.EndRow

See
Cursor
XUsedAreaCursor
XCellCursor
XCellRangeAddressable