In Calc I have an active cell. With keyboard to move to the next cell not empty I type Ctrl+UpArrow or Ctrl+DownArrow or .....
The same is by dispatcher.executeDispatch(oFrame, ".uno:GoUpToStartOfData", "", 0, args1()) and the active cell position change.
Very well !!!
But I'd like to know the cell without move the active cell, the same in Excel with oCell.End(xlStart...) and there it is very simpler.
Here in LO don't find the same property...
In my software I need often to examine this, and the various active cell move the user sheet up e down.. this is not good and the screen refresh slow the macro.
This is a simple problem, but don't have seen simple answers.
Thank you in advance
[Solved] Where is the next cell with data?
[Solved] Where is the next cell with data?
Last edited by nickGiard on Thu Jul 28, 2022 10:56 pm, edited 1 time in total.
LibreOffice 6.3 on Windows 10 64bit
Re: Where is the next cell with data?
It seems more like a design-problem… data should NOT organized semanticly: "one piece here - next piece somewhere in the off"
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
- Sébastien C
- Posts: 111
- Joined: Mon Jan 04, 2010 5:06 pm
- Location: Meymac, France
Re: Where is the next cell with data?
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 .
Re: Where is the next cell with data?
Thank you for the very fast answer, but I don't see (or understand) the answer.
Examples: Have cell A2 filled and A6 filled, and empty between. How can find A6 starting from A2. ???
In excel VBA: A6 = A2.End(xlDown) very very simpler.
Why not in LO ??? can anyone write a solution ??
Thank you in advance
Examples: Have cell A2 filled and A6 filled, and empty between. How can find A6 starting from A2. ???
In excel VBA: A6 = A2.End(xlDown) very very simpler.
Why not in LO ??? can anyone write a solution ??
Thank you in advance
LibreOffice 6.3 on Windows 10 64bit
Re: Where is the next cell with data?
Here is a simple minded macro that steps down the column of the active cell and finds the next cell containing a Value, Datetime, String, Annotation, or Formula. At the end of the code, oCurs points at that cell.
Code: Select all
oSheet = ThisComponent.CurrentController.ActiveSheet
oCurrCell = ThisComponent.CurrentSelection
'Should check if oCurrCell is a single cell
oCurs = oSheet.createCursorByRange(oCurrCell)
oCurs.gotoOffset(0,1)
' See http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html
' for the meaning of the 31 in the next line
While oCurs.queryContentCells(31).Count = 0
oCurs.gotoOffset(0,1)
WEnd
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Where is the next cell with data?
Thank you very much FJCC for your code, it is very interesting e tell me that there isn't one row code to resolve my query.
Your code has a little problem : more space between cells with data, more time need to solve. Testing with GetSystemTicks() for 1000 empty cells give 68
Ticks, for 2000 get 133, for 3000 get 177, for 9000 get 1136 ...If there isn't data below, don't stop o very long time.
I have develop a macro that need 1 to max 2 Ticks and not depend how empty cells are between, and work well also if is missing data.
Here the code for all, if you test it ed improve the solution I appreciate :
I hope this is useful
Your code has a little problem : more space between cells with data, more time need to solve. Testing with GetSystemTicks() for 1000 empty cells give 68
Ticks, for 2000 get 133, for 3000 get 177, for 9000 get 1136 ...If there isn't data below, don't stop o very long time.
I have develop a macro that need 1 to max 2 Ticks and not depend how empty cells are between, and work well also if is missing data.
Here the code for all, if you test it ed improve the solution I appreciate :
Code: Select all
'----------------------------
Function NextCellQuery(oCell, sDULR$, Optional iOffSet%) ' DownUpLeftRight
' The nearest cell not empty near oCell
' ---------
' Function ToRgEnd(oCell, sDULR$, Optional iOffSet%)
If IsMissing(iOffSet) Then iOffSet = 1 ' Dalla cella successiva
Dim oRgToEnd, oCell0
If oCell.ImplementationName = "ScCellObj" Then
oCell0 = oCell
ElseIf oCell.ImplementationName = "ScCellRangeObj" Then
oCell0 = oCell.getCellByPosition(0, 0)
End If
Dim iRow0&, iCol0&, iRowMax&, iColMax&, oSh
'
iCol0 = oCell0.CellAddress.Column
iRow0 = oCell0.CellAddress.Row
oSh = oCell0.SpreadSheet
iRowMax = oSh.RangeAddress.EndRow
iColMax = oSh.RangeAddress.EndColumn
' oRgEnd da Offset cella inizio a fine foglio
Dim oRgEnd
If sDULR = "D" Then ' Down
oRgEnd = oSh.getCellRangeByPosition(iCol0, iRow0+iOffSet, iCol0, iRowMax)
ElseIf sDULR = "U" Then ' Up
If iRow0 = 0 Or iRow0-iOffSet < 0 Then Exit Function ' >>>
oRgEnd = oSh.getCellRangeByPosition(iCol0, 0, iCol0, iRow0-iOffSet)
ElseIf sDULR = "L" Then ' Left
If iCol0 = 0 Or iCol0-iOffSet < 0 Then Exit Function ' >>>
oRgEnd = oSh.getCellRangeByPosition(0, iRow0, iCol0-iOffSet, iRow0)
ElseIf sDULR = "R" Then ' Right
oRgEnd = oSh.getCellRangeByPosition(iCol0+iOffSet, iRow0, iColMax, iRow0)
End If
' ---------
Dim aRgsSpecialCells
aRgsSpecialCells = oRgEnd.queryContentCells(23) ' 1+2+4+16
If aRgsSpecialCells.Count = 0 Then Exit Function '>>>
Dim oNextCell, rg0
If InStr("DR", sDULR) Then ' Down Right
rg0 = aRgsSpecialCells(0)
If rg0.ImplementationName = "ScCellObj" Then
oNextCell = rg0
ElseIf rg0.ImplementationName = "ScCellRangeObj" Then
oNextCell = rg0.getCellByPosition(0, 0)
End If
Else
rg0 = aRgsSpecialCells.getByIndex(aRgsSpecialCells.getCount()-1)
If rg0.ImplementationName = "ScCellObj" Then
oNextCell = rg0
ElseIf rg0.ImplementationName = "ScCellRangeObj" Then
oNextCell = rg0.getCellByPosition(rg0.Columns.count-1, rg0.Rows.Count-1)
End If
End If
NextCellQuery = oNextCell
End Function
LibreOffice 6.3 on Windows 10 64bit