[Solved] Where is the next cell with data?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
nickGiard
Posts: 16
Joined: Fri Nov 10, 2017 5:57 pm

[Solved] Where is the next cell with data?

Post by nickGiard »

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
Last edited by nickGiard on Thu Jul 28, 2022 10:56 pm, edited 1 time in total.
LibreOffice 6.3 on Windows 10 64bit
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Where is the next cell with data?

Post by karolus »

It seems more like a design-problem… data should NOT organized semanticly: "one piece here - next piece somewhere in the off"
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Where is the next cell with data?

Post by Sébastien C »

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 :ouch: .
nickGiard
Posts: 16
Joined: Fri Nov 10, 2017 5:57 pm

Re: Where is the next cell with data?

Post by nickGiard »

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
LibreOffice 6.3 on Windows 10 64bit
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Where is the next cell with data?

Post by FJCC »

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.
nickGiard
Posts: 16
Joined: Fri Nov 10, 2017 5:57 pm

Re: Where is the next cell with data?

Post by nickGiard »

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 :

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
I hope this is useful
LibreOffice 6.3 on Windows 10 64bit
Post Reply