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

Creating Extension - 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 forum is not for asking questions about writing your own macros.

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

Postby Sébastien C » Sun Jan 06, 2019 11:43 pm

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 !

Code: Select all   Expand viewCollapse view
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
(14.1 KiB) Downloaded 144 times
LibreOffice under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
Posts: 93
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

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

Postby Villeroy » Fri Jan 18, 2019 7:39 pm

getUsedRange takes a sheet and returns a range from where you can derive any rows or columns above, below or aside
Code: Select all   Expand viewCollapse view
Function getUsedRange(oSheet)
Dim oCursor
   oCursor = oSheet.createCursor()
   getUsedRange = oCursor
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   Expand viewCollapse view
Function getCurrentRegion(oRange)
Dim oCursor
   oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
   getCurrentRegion = oCursor
End Function

Code: Select all   Expand viewCollapse view
Sub test_UsedRange()
view = thiscomponent.getCurrentController()
sh = view.getActiveSheet()
rg = getUsedRange(sh)
addr = rg.getRangeAddress()
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()
   report addr
   msgbox "No single cell or range selected"
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, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests