BASIC+Calc: How to get address of last cell used with content

lonk · November 17, 2019, 3:51am

This code is for all kinds of cell used (including format).

 Sub GetAddressOfLastCellUsed
	Dim oSheet, oCursor  As Object
	Dim LastColumn, LastRow As Long
	oSheet = thiscomponent.getcurrentcontroller.activesheet
	oCursor = oSheet.createCursor
	oCursor.gotoEndOfUsedArea(False)
	LastColumn = oCursor.RangeAddress.EndColumn
	LastRow = oCursor.RangeAddress.EndRow
	MSgBox "Cell name at the end of used area is " & "(" & LastColumn & "," & (LastRow + 1) & ")"
End Sub

How can we get the address of last cell used with content only (C10) instead of format (D17) ?

mauricio · November 17, 2019, 4:13am

Try:

Sub get_last_content()
	doc = ThisComponent
    sheet = doc.CurrentController.ActiveSheet
    cursor = sheet.createCursor    
    cells = cursor.queryContentCells(1023)
	MsgBox cells(cells.Count - 1).AbsoluteName
End Sub

lonk · November 17, 2019, 5:15am

Dear @Mauricio ,

How can I change from AbsoluteName to position of column index 2 and row index 9 separately ?

mauricio · November 17, 2019, 6:05am

exactly in your example

cell = cells(cells.Count - 1)
LastColumn = cell.RangeAddress.EndColumn
LastRow = cell.RangeAddress.EndRow
MSgBox "Cell name at the end of used area is " & "(" & LastColumn & "," & (LastRow + 1) & ")"

lonk · November 17, 2019, 6:29am

Dear @mauricio ,

Thank you so much.

bloke · August 25, 2021, 7:01am

If i need last used row in Column B ?

Wanderer · August 25, 2021, 7:22am

Then you should start a new question, instead of extending a old topic with barely related additions.

bloke · August 25, 2021, 7:27am

i dont want to do all tat hard work

KamilLanda · August 25, 2021, 8:54am

For example like this

Sub lastRowInColumnB
	dim oDoc as object, oSheet as object, oColumn as object, oContent as object, p()
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oColumn=oSheet.Columns.getByName("B")
	oContent=oColumn.queryContentCells(1023)
	p=oContent.RangeAddresses
	msgbox p(ubound(p)).EndRow 'last row in column B
End Sub

bloke · August 25, 2021, 8:55am

thanks Kamil

Lupp · August 25, 2021, 9:18am

See also: tdf#137667.
CellFlags =1023 is specified to find all usedl cells where “used” should mean the same as in UsedArea. The above suggestion therefore only returns the expected result because the mentioned bug is active. The correct value is CellFlags=31 if cells with annotations (“comments”) shall be included, CellFlags=23 otherwise (direct content of any kind - including formulas).
Please note that there isn’t a single CellFlag for “number” in the ordinary sense Calc uses the term. DateTime is treated differently based on the category of the NumberFormat. To get all the Number cells in the Calc sense, you need to use CellFlags=3 (=1+2)

Also:
The question assumes there is a definite “last used cell” in any sheet. The term is, however, ambiguous not only concerning the “used”: (1)Rightmost used cell of the bottom used row? (2)Bottommost used cell of the rightmost used column? (3)Bottom right cell of the “used rectangular range”? The interpretation (§) was obviously assumed by the OQ. The answer I’m commenting on silently assumed (2) if the actual working is interpreted. Afaik the order of CellRange objects returned by .queryContentCells() isn’t specified at all.

Amendment;
I suppose the mentioned bug will not be fixed. The fix would break too many sheets using code relying on it. The “indispensable bug”. An old phenomenon.

Villeroy · August 25, 2021, 9:32am

This does not work because queryContentCells returns a collection of SheetCellRanges even if it queries one contiguous range of content cells. The address of its last element should return the correct end column.

'calls: none
Function getUsedRange(oSheet)
Dim oCursor
	oCursor = oSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)
	oCursor.gotoEndOfUsedArea(True)
	getUsedRange = oCursor
End Function

returns the used range of a given sheet as a range object. This rectangle includes all content cells.

Villeroy · August 25, 2021, 9:38am

For completeness:

Function getCurrentRegion(oRange)
Dim oCursor
	oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
	oCursor.collapseToCurrentRegion
	getCurrentRegion = oCursor
End Function

returns the current region of adjacent content cells around a given range. In most cases you want to pass a cell object to this function.

Does this forum include a list of foes, by the way?

Lupp · August 25, 2021, 9:41am

This again ignores the OQ’s requirement to not include formatted cells not having actual content.
And to all of us: Having tested a piece of code with INT(PI()/3) examples isn’t a proof of correctness.

bloke · August 25, 2021, 9:44am

This is what im using to return the last used row of any column:

function GetLastRow(sheet, col)

eCells = sheet.Columns(col).QueryEmptyCells
finRg = eCells(eCells.Count - 1)

GetLastRow =finRg.GetCellByPosition(0, 0).RangeAddress.endRow

End Function

‘UsedRange’ is too tricky to use i think

sokol92 · August 25, 2021, 10:27am

In my LO 7.2.0.4, the macro getUsedRange by @Villeroy for the example from start message returns C10 as the last cell.

Villeroy · August 25, 2021, 10:44am

Correct. It works as expected. I made a sheet like the one screenshot with content in A1:C10 and formatting in D17. The following code selects A1:C10.

Sub Main
sh = ThisComponent.Sheets(0)
view = ThisCOmponent.CurrentController()
rg = getUsedRange(sh)
view.select(rg)
End Sub

Lupp · August 25, 2021, 4:42pm

What’s “it”? You posted code based on different approaches. Once you used the (formatting.sensitive!) XUsedAreaCursor with its methods gotoStartOfUsedArea and gotoEndOfUsedArea, already contained in the OQ, otherwise the XSheetCellCursor with its (wrongly named) collapseToCurrentRegion. The latter actually ignores formatting, but looks for a range including the previously covered one and completely surrounded by “empty” cells (or sheet edges). Both approaches can’t actually do what the OQ asked for if we not restrict the question to the one unsubstantial example. Lacking the distinction between the (at least) 3 cases I listed above, the question simply isn’t answerable sensibly.

BTW: V7.2 (now .0.4 for me) comes with a regression bug concerning the XUsedAreaCursor which now wrongly ignores formatted cells. I just wasted another hour to find this out. My preserved 7.1.2 did not yet contain the bug. Do I now need to check again my user profile? It’s a mess…
(Yes. The bug also shows up with a virgin profile.)

Also see: tdf#144085.

Concerning the term current region:
demoSelectCurrentRegion.ods (11.7 KB).

mikekaganski · August 25, 2021, 5:23pm

No, this hour is not wasted - thanks for finding it and filing it!

Villeroy · August 25, 2021, 11:49pm

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XUsedAreaCursor.html

provides methods to find the used area of the entire sheet. The used area is the smallest cell range
hat contains all cells of the spreadsheet with any contents (values, text, formulas) or visible formatting > borders and background color).

I always thought that including the formatting would be a bug and that this bug has been fixed recently. Now I learn that the misbehaviour is intended.