Macro how to get line number of last non-empty cell of a row

English
,
#1

Hi,
I’m just a beginner and I’d like to find the way to know the line number of the first non-empty cell of a row (0).
I have a row (first one) which have data. I just want to get a line of the last cell of those data. I have others rows longer than the first one, so I just want the last of the first row.
I did a lot of search, found gotoEndOfUsedArea(False) but it gives me the first non used line, not the same thing.
And I need to get the line number, not to select the cell.
Thanks
Sorry for my english, but French forum seems not to be very active.

EDIT:
It seems I made a big confusion (bad english).
I want the row number of the first nonblank cell in a column (the first one).
Sorry.

#2

Please clarify: Do you mean the column number of the last non-empty cell of the first row of a Calc document sheet?

#3

It seems I made a big confusion (bad english).

I want the row number of the first nonblank cell in a column (the first one).

Sorry.

#4 
Function getLastContentIndex(iSheet As Long, iCol As Long) As Long
oSheet = ThisComponent.Sheets.getByIndex(iSheet)
oCol = oSheet.Columns.getByIndex(iCol)
oBlanks = oCol.queryEmptyCells()
oRg = oBlanks.getByIndex(oBlanks.getCount()-1)
getLastContentIndex = oRg.RangeAddress.StartRow -1
End Function
#5 
Function getFirstContentIndex(iSheet As Long, iCol As Long) As Long
oSheet = ThisComponent.Sheets.getByIndex(iSheet)
oCol = oSheet.Columns.getByIndex(iCol)
oBlanks = oCol.queryEmptyCells()
oRg = oBlanks.getByIndex(oBlanks.getByIndex(0))
getFirstContentIndex = oRg.RangeAddress.EndRow +1
End Function
#6

Thank you.
I don’t really know how to use this function, but, I always have an error on

oRg = oBlanks.getByIndex(oBlanks.getByIndex(0))

In French:
Erreur d’exécution BASIC
Valeur de propriété incorrecte

#7

I got it with:

oSheet = ThisComponent.Sheets.getByIndex(mySheet)
oCol = oSheet.Columns.getByIndex(myCol)
oBlanks = oCol.queryEmptyCells()
oRg = oBlanks.getByIndex(0)
MsgBox oRg.RangeAddress.StartRow

Thanks !

#8

I posted the first version based on the topic header (last non-empty). This one assumes no content in the last row #1048576. It ignores any block of content until the last cell. I modified the function after you clarified the request. However, getFirstContentIndex assumes that there are blank cells on top. Any block of content at row #1 will be ignored.
And of course,

oBlanks.getByIndex(oBlanks.getByIndex(0))

should be

oBlanks.getByIndex(0)
#9

This should work as expected.

Function getFirstContentIndex(iSheet As Long, iCol As Long) As Long
With com.sun.star.sheet.CellFlags
	iFlag = .VALUE + .STRING + .DATETIME + .FORMULA
End With
oSheet = ThisComponent.Sheets.getByIndex(iSheet)
oCol = oSheet.Columns.getByIndex(iCol)
oRanges = oCol.queryContentCells(iFLag)
oRg = oRanges.getByIndex(0)
getFirstContentIndex = oRg.RangeAddress.StartRow
End Function

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.