How to Find Out in a Particular Column, Last Used Cell or No Of Rows through Macro ,which is Dynamically Increase or Decrease in a Data

[sv.thiyagarajan]

Part 25 - Arrays.ods (22.0 KB)
Dear Friends,
I have a Data which is Dynamically Increase or Decrease and I want to get the Last Used Cell through StarBASIC Macro in a Particular Column, which is dynamically Increase or Decrease.

Say For Example,
Here, My Data Sample Screen Shot

I want to know How Many Rows in a " Column D " and LastUsedCell_(Name or Index) in Column D.
My Starting Cell in Column D is Cell D3 and My Last Cell is in Column D is CellD15.
Please, Note:
The Data May Increase Or Decrease. So, Column D may also Increase or Decrease Dynamically.

Any Code For Macro ( StarBASIC )…

[schiavinatto]

Hi @sv.thiyagarajan, I don’t know how you’re going to use the information, but it’s possible with formulas…

[sv.thiyagarajan]

Thanks For Replay …Hi I got Answers From sokol92 … Using Native StarBASIC Macro…

[jfn]

Have a look at this tread:
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=10817
I guess you’ll find what you’re looking for.

[KamilLanda]

And what your older Ask? There is solution with oCur.collapseToCurrentRegion()

Sub checkLastRow
	dim oDoc as object, oSheet as object, oCell as object, oCur as object
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oCell=oSheet.getCellRangeByName("D3")
	oCur=oSheet.createCursorByRange(oCell) 
	oCur.collapseToCurrentRegion()
	msgbox "Index of last row: " & oCur.RangeAddress.EndRow
End Sub

[sv.thiyagarajan]

Hi KamilLanda,
This is i Know, But, I want Specific or Particular Column C find out LastUsedCell , Which is Dynamically Increase or Decrease Along With Data.
i Want Only Starting Cell in Column C is C3 To EndOfUsedCell or LastRowOfColumn C

In VBA Code
We Can Easily Get…Like This

Dim FlimLengths() As Variant
FilmLengths = Range(“D3”, Range(“D2”).End(xlDown))

Answer is FilmLengths(1 to 13 , 1 to 1 )
Here, 13 Rows are there.

Like This I want to get the Answers in StarBASIC in LibreOffice Macro Code
Any Idea …

[KamilLanda]

All right , I know oCur.queryEmptyCells() for only one column.

Sub checkLastRowInOneColumn
	dim oDoc as object, oSheet as object, oRange as object, oCur as object, oEmpty as object
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oRange=oSheet.getCellRangeByName("C3:C" & oSheet.RangeAddress.EndRow+1) 'column C from 3rd row to last row
	oCur=oSheet.createCursorByRange(oRange)
	oEmpty=oCur.queryEmptyCells() 'get parts with empty cells
	msgbox "Index of last row: " & oEmpty(0).RangeAddress.StartRow-1
End Sub

[sv.thiyagarajan]

Hi KamilLanda, got it Answers From karolus by Python and sokol92 by Native StarBasic Macros … both replied back to back which they replied My Another Post or Topic in the Same Example …

Sorry for the above post which have Typo Error … its Not Column C3 ,
ts Colum D3…

Any Way … Here is the Link … of the Post or Topic

How to Write the StarBASIC code and get the Answers in LibreOffice Like VBA Code below I given? - English - Ask LibreOffice

Original VBA Code :

Sub CalculateWithArray()

Dim FilmLengths() As Variant
Dim Answers() As Variant
Dim Dimension1 As Long, Counter As Long

Sheet1.Activate

FilmLengths = Range("D3", Range("D2").End(xlDown))
Dimension1 = UBound(FilmLengths, 1)

ReDim Answers(1 To Dimension1, 1 To 2)

Counter = 1
For Counter = 1 To Dimension1
    Answers(Counter, 1) = Int(FilmLengths(Counter, 1) / 60)
    Answers(Counter, 2) = FilmLengths(Counter, 1) Mod 60
Next Counter

Range("F3", Range("F3").Offset(Dimension1 - 1, 1)).Value = Answers

Erase FilmLengths
Erase Answers

End Sub

and The Screen Shots of Excel File

Now, StarBASIC Code in LibreOffice - Calc

Option Explicit
Sub simple_basic_foo()
Dim oDoc As Object, oSheet As Object, oCursor As Object, height As Long
Dim out, v, dataArray, i As Long
oDoc = ThisComponent
oSheet = oDoc.Sheets.getByName(“Sheet1”)
oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName(“D3”))
oCursor.collapseToCurrentRegion()

height=oCursor.Rows.Count-2
oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName(“D3”)) 'Return to D3!
oCursor.collapseToSize(1, height)
ReDim out(height-1)
dataArray=oCursor.dataArray
For i=0 To height-1
v=dataArray(i)(0)
out(i)=Array(Int(v / 60), v Mod 60)
Next i

oCursor.gotoOffset(2,0) ’ 2→,0↓
oCursor.collapseToSize(2, height)
oCursor.DataArray = out
End Sub

Here is The LibreOffice - Calc - Screen Shot

Finnally , For Easy Understanding, Mouse Coursor Selet Method (Gui Mode ) I used… Added Extra Code … For Easy Understanding… Purpose for All the People…

Note : Use Mouse Click by Clicking One By One → StepIntoF8 and Don’t use F8 in Key Board

Sub simple_basic_foo()
Dim oDoc As Object, oSheet As Object, oCursor As Object, height As Long
Dim out, v, dataArray, i As Long

  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByName("Sheet1")
  
  oDoc.CurrentController.setActiveSheet(oSheet)
  
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3"))
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToCurrentRegion()
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
    
  height=oCursor.Rows.Count-2
  oCursor = oSheet.createCursorByRange(oSheet.getCellRangeByName("D3")) ' Return to D3!
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToSize(1, height)
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
  
  ReDim out(height-1)
  dataArray=oCursor.dataArray
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(dataArray)
  For i=0 To height-1
    v=dataArray(i)(0)
    out(i)=Array(Int(v / 60), v Mod 60) 
  Next i
    
  oCursor.gotoOffset(2,0) ' 2→,0↓
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
  
  oCursor.collapseToSize(2, height)
  View = ThisComponent.CurrentController : frame = View.getFrame : frame.Activate : View.Select(oCursor)
  oCursor.DataArray = out

End Sub

Thats All l…