[Calc] Know the last nonempty cell of a row or a column.

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Calc] Know the last nonempty cell of a row or a column.

Post by Sébastien C »

Hello everyone,

Following a very rich question on this thread, I found myself having to know the number of the last line occupied by a non-empty cell in a given column. If the thing is very simple to do with VBA (for example [Cells.Rows.Count, "C"]), it is a little bit more complicated to do with the API. But it is thanks to the intervention and the propositions of mikele that it is possible to design a function knowLastCell() which operates a search of not empty cells, on a column or a given line (one specifies a type for that). The rangeAddresses property of the queryEmptyCells's object contains one or more arrays. We always take the last one. And we extract, depending on the type of search that we do, the startRow or startColumn property, minus one, that the function returns.

Enjoy !
:D

Code: Select all

Option explicit

Sub Main
  ' Search the number of the last line with something in cell in the column number 2.
  msgBox knowLastCell("Sheet1", 2, 0)

  ' Search the number of the last column with something in cell in the line number 12.
  msgBox knowLastCell("Sheet1", 12, 1)
End Sub

' If myType = 0, the search is do on the column.
' If myType = 1, the search is do on the line.
Function knowLastCell(mySheetName As String, myColumnOrMyLine As Long, myType As Integer) As Long
  Dim mySheet As Object, myBlock As Object, myFound As Object

  On Error Goto myError
  mySheet = thisComponent.sheets.getByName(mySheetName)

  Select Case myType
   Case 0 : myBlock = mySheet.columns(myColumnOrMyLine)
   Case 1 : myBlock = mySheet.rows   (myColumnOrMyLine)
  End Select
   
  myFound = myBlock.queryEmptyCells.rangeAddresses

  With myFound(uBound(myFound))
   Select Case myType
    Case 0 : knowLastCell = .startRow    - 1
    Case 1 : knowLastCell = .startColumn - 1
   End Select
  End With

  Exit Function

  myError:                                                   ' If the column or the line is empty.
  knowLastCell = -1
End Function
Attachments
functionKnowLastCell.ods
(14.1 KiB) Downloaded 368 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Know the last nonempty cell of a row or a column.

Post by Villeroy »

getUsedRange takes a sheet and returns a range from where you can derive any rows or columns above, below or aside

Code: Select all

Function getUsedRange(oSheet)
Dim oCursor
	oCursor = oSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)
	oCursor.gotoEndOfUsedArea(True)
	getUsedRange = oCursor
End Function
 Edit: The above function may include empty but formatted cells. The following version gets the rectangle that includes all constants and formulas: 

Code: Select all

Function getUsedRange(oSheet)
Dim oCursor1, oCursor2, addr
	oCursor1 = oSheet.createCursorByRange(oSheet)
	oCursor1.gotoStart()
	addr = oCursor1.getRangeAddress()
	oCursor2 = oSheet.createCursorByRange(oSheet)
	oCursor2.gotoEnd()
	addr.EndColumn = oCursor2.RangeAddress.EndColumn
	addr.EndRow = oCursor2.RangeAddress.EndRow
	getUsedRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn,  addr.EndRow)
End Function
Quite often the concept of "current region" is preferable:
getCurrentRegion gets a cell (or range) and returns a range which includes all adjacent non-blanks around the given cell. This is the same concept as with all the data tools where you select one cell within a list and the sorting, filtering, subtotal, pivot tool expands the single cell selection to the current region.

Code: Select all

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

Code: Select all

Sub test_UsedRange()
view = thiscomponent.getCurrentController()
sh = view.getActiveSheet()
rg = getUsedRange(sh)
addr = rg.getRangeAddress()
view.select(rg)
report addr
End Sub

Sub test_CurrentRegion()
view = thiscomponent.getCurrentController()
sel = view.getSelection()
if sel.supportsService("com.sun.star.sheet.SheetCellRange") then
	rg = getCurrentRegion(sel)
	addr = rg.getRangeAddress()
	view.select(rg)
	report addr
else
	msgbox "No single cell or range selected"
endif
End Sub

Sub report(a)
print "sheet: ", a.Sheet, _
  "start col: ", a.StartColumn, _
  "start row: ", a.StartRow, _
  "last col: ", a.EndColumn, _
  "last row: ", a.EndRow
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply