count the number of rows to first page break

[dantheman9]

Hi,

can someone help modify the below code to count the row of the first page break only?

 For i = 1 To ActiveSheet.HPageBreaks.Count
  rowno = ActiveSheet.HPageBreaks(i).Location.Row
  Next

thanks

Dan

 

[jasonb75]

Just a guess

rowno = ActiveSheet.HPageBreaks(1).Location.Row

 

[dantheman9]

thanks Jason,

seems to work now and again. I think I have other issues with the code.
Basily I need to work out the location of the first Page break which is set after formating the page forcing the page to be 1 page wide max.
Once the scaling is done, and i have the row location of the first page break, then all other sheets will be formated to insure best use of the page, and avoid data tables ending up been split over two pages;

 With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&G"
        .CenterHeader = "&""Bold""&14&U" & eventname & Chr(10) & eventname1 & Chr(10) & Chr(10) & "&G"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&7&P"
        .LeftMargin = Application.InchesToPoints(0.708661417322835)
        .RightMargin = Application.InchesToPoints(0.708661417322835)
        .TopMargin = Application.InchesToPoints(1.2)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = False
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
 
ActiveSheet.DisplayPageBreaks = True
 
    Dim rowno As Integer
 
[COLOR=red] rowno = ActiveSheet.HPageBreaks(1).Location.Row[/COLOR]
 
  
  If rowno < 47 Then
  rowno = 47
  End If
 
 
    PgSize = rowno   '  Assumes 49 rows per page
    Set rStart = Range("A3")
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
 
 
    Do
        Set TestCell = rStart.Offset(PgSize, 0)
        If Len(TestCell) = 0 Or Len(TestCell.Offset(-1, 0)) = 0 Then
                Set rEnd = TestCell.End(xlUp)
            Else
                Set rEnd = TestCell.End(xlUp).End(xlUp)
        End If
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=rEnd.Offset(1, 0)
        Set rStart = rEnd.Offset(1, 0)
 
    n = n + 1
    If n > 1000 Then Exit Sub   '  Escapes from an infinite loop if code fails
    Loop Until rStart.Row > lastrow - (rowno - 2)

Stil having issues at the red marked line!

Cheers

Dan

 

[jasonb75]

Maybe the problem is coming from

If rowno < 47 Then
  rowno = 47
  End If
 
 
    PgSize = rowno   '  Assumes 49 rows per page

What if the assumption is incorrect?

Personally, when trying to print in that way I would loop throught the tables on the principle of select, fit to page, print, repeat.

But I'm not so sure something like that would meet your requirements, and if it did, you would still need a consistand way to identify the correct rows to start and end the print for each table.

 

[dantheman9]

Hi Jason,

From some more googling it seems that in order for PageBreak location to work, the actual break needs to be in the active display window! (hence why it seemed to work sometimes after i looks about the sheet in break mode and then restarted the code).

Bit of a pain, But still! At least I know why its not working!

don't surpose you have a quick and easy method to view to the first break?

 

[jasonb75]

I could be wrong, but think that setting zoom to false and using 'fit to pages' disables normal page breaks, possibly you could see the row number with a messagebox

msgbox activesheet.hpagebreaks(1).location.row