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