Find Last Row with data in a column

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
frozbie
Posts: 15
Joined: Thu Sep 18, 2008 10:25 am

Find Last Row with data in a column

Post by frozbie »

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

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

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/ ... &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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find Last Row with data in a column

Post by Villeroy »

Array formula:

Code: Select all

{=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
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

Post by martius »

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

'------------------------------------------------------------------------
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
JohnV
Volunteer
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Find Last Row with data in a column

Post by JohnV »

Here's yet another way.

Code: Select all

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
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Find Last Row with data in a column

Post by JohnSUN-Pensioner »

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
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

Post by martius »

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

'------------------------------------------------------------------------
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
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Find Last Row with data in a column

Post by JohnSUN-Pensioner »

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

	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
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

Post by martius »

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

'------------------------------------------------------------------------
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
timetraveller
Posts: 1
Joined: Sat Jun 21, 2014 5:52 pm

Re: Find Last Row with data in a column

Post by timetraveller »

It is very simple ;-)

Code: Select all

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
Jtrader
Posts: 29
Joined: Thu Oct 24, 2013 3:39 pm

Re: Find Last Row with data in a column

Post by Jtrader »

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
Gaetanopr
Posts: 12
Joined: Sun Jan 27, 2013 11:04 pm

Re: Find Last Row with data in a column

Post by Gaetanopr »

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

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

Re: Find Last Row with data in a column

Post by Villeroy »

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
Post Reply