[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 !
:D

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
Attachments
functionKnowLastCell.ods
(14.1 KiB) Downloaded 6 times
LibreOffice 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 71
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 0 guests