Last Row of each page

[Jonjozz]

Is it possible to find the last row of each printable page on spreadsheet calc? It should be equivalent to “ActiveSheet.HPagebreaks(1).Location” in Excel macro command.

[Zizi64]

What do you want to achieve really? Maybe we can suggest you an another way - if we know your task.

[Jonjozz]

The code fills the second column. Each page restarts the count.
The Excel macro code is simple:
NumPages = ExecuteExcel4Macro(“Get.document(50)”)
For i = 2 To NumPages
Num = 1
Set celula = ActiveSheet.HPageBreaks(i).Location
Set LastCell = celula.Offset(-1, 0)
RowLastCell = LastCell.Row
If RowLastCell > lastRow Then RowLastCell = lastRow
For k = NextCell To RowLastCell
Range(“B” & k).Value = Num
Num = Num + 1
Next k
NextCell = celula.Row
Next

This macro code is in “BeforePrint Event”

[Jonjozz]

I get The NumPages from libreoffice code:
NumPages=ThisComponent.getRendererCount(ThisComponent.Sheets(4), Array())
I need the equivalent ActiveSheet.HPageBreaks(i).Location to complete the macro.

[Jonjozz]

Anybody? No one? Nothing?

[sokol92]

Virtually unchanged.

' Fills column B with the line number of the print page 
Sub TestNum
  Dim oSheet, oHPageBreaks, NumPages As Long, i As Long, n as Long, j as Long, LastRow As Long 
  oSheet=ThisComponent.CurrentController.ActiveSheet
  NumPages = ThisComponent.getRendererCount(oSheet, Array())
  oHPageBreaks=oSheet.RowPageBreaks
  For i = 1 To NumPages
    n=oHPageBreaks(i-1).Position - 1
    For j=LastRow To n 
      oSheet.getCellByPosition(1, j).setvalue j-LastRow + 1
    Next j
    LastRow=n+1
  Next i
End Sub      

Or so:

' Fills column B with the line number of the print page 
Sub TestNum2
  Dim oSheet, i As Long, n As Long
  oSheet=ThisComponent.CurrentController.ActiveSheet
  For i=0 To Sheet_UsedRange(oSheet).RangeAddress.EndRow
    n=n+1
    If oSheet.Rows(i).IsStartOfNewPage Then n=1
    oSheet.getCellByPosition(1, i).setvalue n
  Next i  
End Sub

' Returns the range of used cells for oSheet.
Function Sheet_UsedRange(ByVal oSheet)
    Dim oCursor
    oCursor = oSheet.createCursor
    oCursor.gotoEndOfUsedArea True
    Sheet_UsedRange = oCursor
End Function  

[Jonjozz]

Man, You are The One. The solution is very very smart and beautiful. Thanks a trillion.

[sokol92]

Good luck!

[Zizi64]

With User defined cell function:

Function IsPageBreakInRow(iSheet as integer, iRow as long) as boolean
Dim oSheet As Object
 oSheet = ThisComponent.Sheets.getbyindex(iSheet-1)
 IsPageBreakInRow = oSheet.Rows(iRow).IsStartOfNewPage
End function

PageBreaks.ods (10.4 KB)

[Jonjozz]

Thanks, I will certainly test that solution. You all are Great. Thanks for your attention and job. I appreciate it enormously.

[Zizi64]

(You must recalculete the document or you must re-enter the initial value (yellow cell in my sample) after you insert a new page break onto the sheet or when you delete a page break from the sheet.)