Find Last Row with data in a column

Creating a macro - Writing a Script - Using the API

Find Last Row with data in a column

Postby frozbie » Fri Oct 17, 2008 9:56 am

Hi,

I've been searching for a way to find the last or bottom row in a specific column, where the cell is not empty. I've now got several methods and am posting this simply in the hope there is a more efficient way of doing it. Also, I've realised while searching that this probably should not be as simple as I originally wanted as there is a need to validate what I find.

Methods I have as follows:

1. Use a cursor and GotoEndOfUsedArea and work backwards from there. This will work but is not ideal though thinking about it, other methods have no validation either, so once build validation in, may be as efficient. Reason this is not ideal is that the column I am interested in is likely to have a different number of rows of data than the EndOfUsedArea. Ideally, I need only the EndOfUsedArea for a specific column and in testing, I could only get for the whole spreadsheet.

Code: Select all   Expand viewCollapse view
Sub LastRowNumber()
'// Test sub to Find last row of data in a column
'// See http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Cell_Cursor
'// and interface XCellCursor
Dim oDoc As Object
dim lastRow as Long
dim oSheet as object
Dim oCol
   
oDoc = ThisComponent
oSheet = oDoc.getSheets().getByName("Sheet3")
   
oCol = oSheet.getColumns().getByIndex(5)
'// Was wondering if there is a method to only search in specific column...
oDoc.getCurrentController().select(oCol)

Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
dim oRange as Object
oRange = oSheet.getCellRangeByPosition(3,4,3,50)
oCursor = oSheet.createCursorByRange(oRange)
oCursor.GotoEndOfUsedArea(false)
lastrow = oCursor.RangeAddress.EndRow '// this gives the row number of last row with data in it
print lastrow
end sub


2. Record a macro going from bottom of sheet to first (or last depending on how you look at it) filled cell.
This method seems especially clunky and yet, is probably closest to what I used to use in Excel

Code: Select all   Expand viewCollapse view
sub FindData
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$D$65536"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args7())
dim oActiveCell
dim oConv
oActiveCell = ThisComponent.CurrentSelection
print oActiveCell.getCellAddress().Row
end sub


3. Use a search method to look for cell containing a key word (in this case, I am expecting an end of data tag to be found) and then return the row. Found this useful entry on the forum:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=9897&start=0&st=0&sk=t&sd=a
and the actual search function in AndrewMacro.

So, if I have a question it is, does anyone know of a more efficient way to search down a single column or have I uncovered them all?

Mark
OOo 3.0.X on MS Windows Vista + Windows XP
User avatar
frozbie
 
Posts: 15
Joined: Thu Sep 18, 2008 10:25 am

Re: Find Last Row with data in a column

Postby Villeroy » Fri Oct 17, 2008 1:43 pm

Array formula:
Code: Select all   Expand viewCollapse view
{=MAX(NOT(ISBLANK($SheetX.A$1:A$65536))*ROW($SheetX.A$1:A$65536))}

Use anywhere and copy to the right. Don't drag at the tiny handle in this case. Copy, select target range and paste.
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
User avatar
Villeroy
Volunteer
 
Posts: 29711
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find Last Row with data in a column

Postby martius » Mon Dec 26, 2011 1:25 am

If you want to get the index of the last row with data in a column, without moving the cursor, you can also use this function below. You just need to pass the parameters "index of the column" and "index of the sheet" you want.
Code: Select all   Expand viewCollapse view
'------------------------------------------------------------------------
Sub example
   x = LastRowWithData(0, 0)'let's supose column "A" of first sheet
end sub
'------------------------------------------------------------------------
Function LastRowWithData (ColumnIndex as long, SheetIndex as long) as long
   Dim oCursor As Object, oRange As Object, oSheet As Object
   Dim LastRowOfUsedArea as long, R as long
   Dim RangeData

   oSheet = ThisComponent.Sheets(SheetIndex)
   oCursor = oSheet.createCursor
   oCursor.gotoEndOfUsedArea(False)
   LastRowOfUsedArea = oCursor.RangeAddress.EndRow
   oRange = oSheet.getCellRangeByPosition(ColumnIndex, 0, ColumnIndex, LastRowOfUsedArea)
   oCursor = oSheet.createCursorByRange(oRange)
   RangeData = oCursor.getDataArray

   For R = UBound(RangeData) To LBound(RangeData) Step - 1
       If RangeData(R)(0) <> "" then
          LastRowWithData = R
          Exit Function
       End If
   Next
End Function
'------------------------------------------------------------------------
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Find Last Row with data in a column

Postby JohnV » Tue Dec 27, 2011 12:36 am

Here's yet another way.
Code: Select all   Expand viewCollapse view
sub LastRowNumber()
'// Test sub to Find last row of data in a column
'// See http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Cell_Cursor
'// and interface XCellCursor
Dim oDoc As Object
dim lastRow as Long
dim oSheet as object
Dim oCol,rd,find,aray
oDoc = ThisComponent
oSheet = oDoc.Sheets().getByName("Sheet1")
oCol = oSheet.getColumns().getByIndex(1)
rd = oCol.createReplaceDescriptor
rd.searchRegularExpression = true
rd.setSearchString(".")
find = oCol.FindAll(rd)
aray = Split(find.AbsoluteName,"$")
lastRow = aray(ubound(Aray))
Print lastRow
end sub
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Find Last Row with data in a column

Postby JohnSUN-Pensioner » Tue Dec 27, 2011 12:36 pm

To solve the problem, let us agree that we are looking for the index of last row with the filled cell.

I want to ask some additional questions.
2frozbie: What should mean "where the cell is not empty"? For example, the cell with the formula =IF(TODAY()=DATE(2012;1;1);"Happy New Year!";"") is empty?.. or is full?
2martius: It might be better to use RangeData = oCursor.getFormulaArray ()?
2JohnV: You've probably had in mind lastRow = Val(aray(ubound(Aray)))-1 ?
2All: What do you think should be done with absolutely empty column? Some of the proposed solutions will not work with such data.

I really like the suggestion Villeroy. Particularly in the embodiment which has offered Ken Johnson - not an array formula, but SUMPRODUCT(). It's just a personal preference - I do not like array formulas for one cell. Especially, if you believe that description, then the default value of property IsArrayFunction is true.
But I do not know how to put this formula to FunctionAccess. :oops:
Maybe someone knows how to set parameters in the range of cells from one column from the first cell to cell which found by gotoEndOfUsedArea?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 845
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Find Last Row with data in a column

Postby martius » Wed Dec 28, 2011 4:16 pm

I think if we use “getFormulaArray” instead of “getDataArray” in “RangeData = oCursor.getDataArray” of my function, it doesn´t make any diference. Even if we have a cell with a formula like in JohnSUN-Pensioner example, “getDataArray” still works.

Anyway, I’ve changed my function to the code of JohnV, because I realized that a ReplaceDescriptor (or a SearchDescriptor) works too much faster in big sheets than an array of data. Do a test: put some data in the row 1048576 of the column “A” and try to use my function to find the last empty row of the column “B”, for example. It demands a lot of time! A code with a SearchDescriptor do not have this problem.

In the other hand, I made some changes in the JohnV code:

1) I used the “lastRow = Val(aray(ubound(aray)))-1”, like JohnSUN-Pensioner suggested. It returns the index of the row instead of the number.

2) I used an If-Then-Else to test if the result of the search is null. If it is null, the whole column is absolutely empty (no data in any row). If the column is absolutely empty, the function returns “-1” (If you prefer, you can change the code to return anything else).

3) Now you can pass the parameters (column and sheet) by index or by name. And you can also omit the sheet. The sheet is an optional parameter now. In this way, if you do not pass the parameter sheet, the function assumes the “active sheet”.

Here you are the whole thing:
Code: Select all   Expand viewCollapse view
'------------------------------------------------------------------------
Sub Example
'x = LastRowIndex("a", "Sheet3") 'column "A" of a sheet named Sheet3
'x = LastRowIndex(5, "Sheet3") 'column "F" of a sheet named Sheet3
'x = LastRowIndex("b", 2) 'column "B" of the third sheet
x = LastRowIndex("b") 'column "B" of the active sheet
'x = LastRowIndex(4) 'column "E" of the active sheet
End sub
'------------------------------------------------------------------------
Function LastRowIndex (InformedColumn, Optional InformedSheet) as long
'this function returns the index of the last row with data in a column
'it returns -1 if the whole column is empty
   Dim oSheet As Object, C as Long
   Dim oColumn As Object, oFinder As Object, oResult as object
   Dim PartsOfTheName   
   
   '------- Sheet -------
   If IsMissing(InformedSheet) then
      oSheet = ThisComponent.CurrentController.ActiveSheet
   ElseIf IsNumeric(InformedSheet) then
      oSheet = ThisComponent.Sheets(InformedSheet)
   Else
      oSheet = ThisComponent.Sheets.GetByName(InformedSheet)
   End If

   '------- Column -------
   If Not IsNumeric(InformedColumn) then
      Dim AllColumnNames (0 to 1023)      
      AllColumnNames = oSheet.Columns.ElementNames
      For i = 0 to 1023
         If AllColumnNames(i) = UCase(InformedColumn) then
            C = i
         End If
      Next
   Else
      C = InformedColumn
   End If

   '------- Search -------
   oColumn = oSheet.Columns(C)
   oFinder = oColumn.createSearchDescriptor
   oFinder.searchRegularExpression = true
   oFinder.SearchString = "."
   oResult = oColumn.FindAll(oFinder)

   '------- Row Index -------
   If Not IsNull(oResult) then
      ResultName$ = oResult.AbsoluteName
      PartsOfTheName = Split(ResultName,"$")
      LastRowIndex = Val(PartsOfTheName(ubound(PartsOfTheName))) - 1
   Else
      LastRowIndex = - 1
   End If
End Function
'----------------------------------------------------------
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Find Last Row with data in a column

Postby JohnSUN-Pensioner » Wed Dec 28, 2011 5:35 pm

Yes, martius, you're right - this version (with search descriptor) is much better.
Unfortunately, the latest bug in the program is only the penultimate bug. Always...
What happens if InformedColumn not found in oSheet.Columns.ElementNames?
The function returns the number of last used row in column AMJ (1023).
What happens if InformedColumn=2048? Or -7?
I may be wrong, but instead the cycle could be used such construction:
Code: Select all   Expand viewCollapse view
   oColumns = oSheet.getColumns()
   If IsNumeric(InformedColumn) then
      If (InformedColumn >= 0) AND (InformedColumn < oColumns.getCount()) Then
         oColumn = oColumns.getByIndex(InformedColumn)
      Else
         LastRowIndex = -2 ' Wrong number
         Exit Function
      EndIf
   Else
      If oColumns.hasByName(InformedColumn) Then
         oColumn = oColumns.getByName(InformedColumn)
      Else
         LastRowIndex = -3 ' Wrong name
         Exit Function
      EndIf
   EndIf
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 845
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Find Last Row with data in a column

Postby martius » Thu Dec 29, 2011 9:43 pm

Very well, JohnSUN-Pensioner,

The most dangerous bug is when the name of the column is invalid and the function assumes column “A” (column “A”, not “AMJ” as you said).
So, after the corrections, I think the function could be like that:
Code: Select all   Expand viewCollapse view
'------------------------------------------------------------------------
Sub Example
'x = LastRowIndex("a", "Sheet3")
'x = LastRowIndex(5, "Sheet3")
'x = LastRowIndex("b", 2)
x = LastRowIndex("xyzxyz")
'x = LastRowIndex(2000)
'x = LastRowIndex("f", "Sheet3")
'x = LastRowIndex("b", 3)
'x = LastRowIndex("b")
'x = LastRowIndex(5)
End sub
'------------------------------------------------------------------------
Function LastRowIndex (InformedColumn, Optional InformedSheet) as long
'this function returns the index of the last row with data in a column
'it returns -1 if the whole column is empty
'it returns -2 if the index of the sheet does not exist
'it returns -3 if the name of the sheet does not exist
'it returns -4 if the index of the column does not exist
'it returns -5 if the name of the column does not exist

   Dim oSheets As Object, oSheet As Object, C as Long
   Dim oColumns  As Object, oColumn As Object, oFinder As Object, oResult as object
   Dim PartsOfTheName   
   
   '------- Sheet -------
   oSheets = ThisComponent.getSheets
   If IsMissing(InformedSheet) then
      oSheet = ThisComponent.CurrentController.ActiveSheet
   ElseIf IsNumeric(InformedSheet) then
      If (InformedSheet >= 0) And (InformedSheet < oSheets.Count) Then
         oSheet = oSheets(InformedSheet)
      Else
           LastRowIndex = -2 'Wrong number
           Exit Function      
      End If
   Else
      If oSheets.hasByName(InformedSheet) Then
         oSheet = oSheets.GetByName(InformedSheet)
      Else
           LastRowIndex = -3 'Wrong name
           Exit Function      
      End If
   End If

   '------- Column -------
    oColumns = oSheet.getColumns()
    If IsNumeric(InformedColumn) then
       If (InformedColumn >= 0) AND (InformedColumn < oColumns.getCount()) Then
          oColumn = oColumns.getByIndex(InformedColumn)
       Else
          LastRowIndex = -4 ' Wrong number
          Exit Function
       EndIf
    Else
       If oColumns.hasByName(InformedColumn) Then
          oColumn = oColumns.getByName(InformedColumn)
       Else
          LastRowIndex = -5 ' Wrong name
          Exit Function
       EndIf
    EndIf

   '------- Search -------
   oFinder = oColumn.createSearchDescriptor
   oFinder.searchRegularExpression = true
   oFinder.SearchString = "."
   oResult = oColumn.FindAll(oFinder)

   '------- Row Index -------
   If Not IsNull(oResult) then
      ResultName$ = oResult.AbsoluteName
      PartsOfTheName = Split(ResultName,"$")
      LastRowIndex = Val(PartsOfTheName(ubound(PartsOfTheName))) - 1
   Else
      LastRowIndex = - 1
   End If
End Function
'----------------------------------------------------------


You can still pass the parameters (column and sheet) by index or by name. And you can also omit the sheet, that is an optional parameter (the function assumes the “active sheet”, if is missing the sheet).
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Find Last Row with data in a column

Postby timetraveller » Sat Jun 21, 2014 6:12 pm

It is very simple ;-)

Code: Select all   Expand viewCollapse view
Sub GetLastRow(LastRow)
   Dim oSheet As Object
   oSheet= thiscomponent.getcurrentcontroller.activesheet
   oCursor= oSheet.createCursor
   oCursor.gotoEndOfUsedArea(False)
   LastRow= oCursor.RangeAddress.EndRow
End Sub
OpenOffice 4.0.1 on Windows XP
timetraveller
 
Posts: 1
Joined: Sat Jun 21, 2014 5:52 pm

Re: Find Last Row with data in a column

Postby Jtrader » Thu Jul 17, 2014 6:46 pm

May not be helpful, but here is the overly simplistic non-programmer way I retrieve the final value in a column that has varying length when sampling different data sets.

1. Count the items in the column using a max that is well beyond possibility to determine how many rows there are. For example in cell C3 use: =Count(A1:A1000000)
2. Once the count is returned use the offset command to return the value of the last row in that column. In cell C4 use: =Offset(A1;C3;0;1)

Those two sets should return the quantity of rows in the data set in cell C3 and the value of the last populated cell in cell C4.

Hope that is relevant and worthy of contribution...

Jtrade.
OpenOffice 4.1
Jtrader
 
Posts: 29
Joined: Thu Oct 24, 2013 3:39 pm

Re: Find Last Row with data in a column

Postby Gaetanopr » Thu Jul 17, 2014 7:54 pm

Hello, I use a query where I point the data to search for Example 1 (VALUE) +2 (DATETIME) +4 (STRING), in a column, starting from the first row to the last of the sheet.
Code: Select all   Expand viewCollapse view
Function LastRowInColonna(oSheet As Object, Col As Long) As Long
  Dim c As Object, oRangePiena As Object, LastRow As Long
  c = oSheet.createCursor
  c.gotoEndOfUsedArea(false)
  LastRow = c.RangeAddress.EndRow
  oRangePiena = oSheet.getCellRangeByPosition(Col, 0, Col, LastRow).queryContentCells(1+2+4).RangeAddresses
    If Ubound(oRangePiena) < 0 Then
       LastRowInColonna = - 1  ' 0
    Else   
       LastRowInColonna = oRangePiena(Ubound(oRangePiena)).EndRow
    End if 
    print LastRowInColonna   
End Function
OpenOffice 3.4 on windows vista / windows XP
Gaetanopr
 
Posts: 11
Joined: Sun Jan 27, 2013 11:04 pm

Re: Find Last Row with data in a column

Postby Villeroy » Thu Jul 17, 2014 9:43 pm

Ctrl+End
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
User avatar
Villeroy
Volunteer
 
Posts: 29711
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests