[Solved] LASTROW function

[jess66]

I'm a newbie to OpenOffice. Some of my MS Office files use a LASTROW function that picks a value from the last row with entries on a spreadsheet. Is there a LASTROW function available for OpenOffice?

[TerryE]

Neither Excel nor Calc have a LASTROW function. In both you can write a Custom Basic function to return this for you.

[kingfisher]

If you have data arranged by row chronologically with the dates in one column, you could use VLOOKUP in conjunction with MAX or DMAX.

[probe1]

this one is take from colorRows

Code: Select all

'...
oDoc = ThisComponent
'...
actSheet = oDoc.currentController.ActiveSheet
vLastPos = FUNC_LastUsedCell(actSheet)
lRows = vLastPos(0)
' ...

' and the funtion used
' -------------------------------------------------------------------
' function uses variant array to return more than one value
Function FUNC_LastUsedCell(oSheet as Object) as Variant

oCursor = oSheet.createCursor()

oCursor.gotoEndOfUsedArea(TRUE) 
oEndAdr = oCursor.getRangeAddress

Dim vLastUsedCell(1) as Variant
vLastUsedCell(0) = oEndAdr.EndRow
vLastUsedCell(1) = oEndAdr.EndColumn 
FUNC_LastUsedCell = vLastUsedCell()

End Function

Does this help?

[posting from vacation: 8°52'55"N 98°16'1" E; actually 29°C]

[Villeroy]

Apply a userdefined style to a whole column and the used area reaches to the bottom of the sheet.

[jess66]

Since I already have a LASTROW function in XL, (part of a Morefunc add-on), my question should have been: How do I make this add-on accessible to Calc?

[Villeroy]

Addons use special "communication channels" to interact with the application they are written for. The code assumes that it is integrated into a particular application to communicate with. It is technically impossible to use add-ons with a third party application.

[JohnV]

Here's my effort to return the value of some cell in the last row. You must define the value of the col variable.

Code: Select all

sub LastRowContentOfSomeCell
dim document   as object,col
col = 0 'Columns are counted from 0 so this is column A.
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = false
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args1())
Dim oCell,oSheet,targetCell
oCell = ThisComponent.CurrentSelection
oSheet = ThisComponent.Sheets.getByIndex(oCell.CellAddress.Sheet)
targetCell = oSheet.getCellByPosition(col,oCell.CellAddress.Row)
MsgBox targetCell.String
end sub

[probe1]

Apply a userdefined style to a whole column and the used area reaches to the bottom of the sheet.

No, it does not***

Apply a user defined formular to a column will do - but that was not the question, was it?

***[tested with OOo 2.1 on Suse 9.3 here - still on holidays with an "old" notebook]

[Villeroy]

Sorry, whole column does not hold true, but have a look at the attachment (contains macro, push hyperlink @A1)

[jess66]

I've found the VLOOKUP function works for me. The first column is a continuous ascending date with entries almost every day, so the following works even if the date is off a day or two from TODAY.

Thanks for the help

VLOOKUP(TODAY();array;index)