Page Breaks and Subscript out of Range

[Farscape2020]

I am writing a macro that is dividing a list of data into worksheets organized by location. When the worksheet is created, the data is divided into three sections (each with their own title and column headings). If a section crosses an automatic page break I would like to add the section title and column headings just after the page break. This involves creating an array of where the horizontal page breaks are located (row numbers), adding the section title and column headings, if needed, then recreating the array where the horizontal page breaks are located in case they have shifted to accommodate the new rows. See the code below.

Private Sub createPageBreak(ws As Worksheet, searchString As String)

    Dim pageBreaks As Variant
    Dim i As Integer

    pageBreaks = findPageBreaks(ws)
   
    For i = LBound(pageBreaks) To UBound(pageBreaks)
   
        add section title and column headings
       
        pageBreaks = findPageBreaks(ws)
   
    Next i
   
End Sub

Because each section has its own title and column headings, freeze panes and print titles will not be useful.

To check for page breaks, I

• find the last row with data (usually within the first 3 or 4 columns) and select that cell
• change the view to page break view
• add all the horizontal page break rows to an array
• change the view back to normal view

I run this procedure each time a new heading is added to be sure I use the most up-to-date page break. The procedure that I use is:

Function findPageBreaks(ws As Worksheet) As Variant

    Dim arr() As Variant
    Dim HPB As HPageBreak
    Dim i, lastRow, rows, maxValue, column As Integer
   
    maxValue = 1
   
    For i = 1 To 5
   
        rows = findLastRow(ws, Number2Letter(CInt(i)))
     
        If rows > maxValue Then
       
            maxValue = rows
            column = i
       
        End If

    Next i
   
    ws.Range(Number2Letter(column) & maxValue).Select
   
    ActiveWindow.View = xlPageBreakPreview
   
    ReDim arr(0 To ws.HPageBreaks.count)
   
    arr(0) = 1
   
    If ws.HPageBreaks.count <= 0 Then
   
        findPageBreaks = arr
        Exit Function
       
    End If
   
    i = 1

    For Each HPB In ws.HPageBreaks
   
        arr(i) = HPB.Location.row
        i = i + 1
       
    Next
   
    ActiveWindow.View = xlNormalView
   
    findPageBreaks = arr

End Function

Everything works fine for the first eight worksheets. On worksheet number nine, it begins the with four automatic page breaks. After it inserts the first section heading at the top of page 2, it check again and there are 5 automatic page breaks (an extra page was added). I then add the section heading at the top of the 3rd page. However, on the 3rd automatic page break (top of the fourth page) I get the following error:

Run-time error '9': Subscript out of range​

When I hover over HPB in the for loop, it states the value is nothing.

Would someone be able to provide insight as to why I am seeing an error at this point in the macro? I can understand if it simple did not work and I needed another approach but, as I outlined, it worked on the previous eight worksheets with between 2 and 4 pages per worksheet and it also worked for the first two page breaks on the 9th worksheet.

Thank you.

 

[Farscape2020]

I found a solution. According to this page on HPageBreaks object, for an automatic print area, the HPageBreaks property applies only to the page breaks within the print area. I used the command

ws.PageSetup.PrintArea = "$A$1:$" & lastColumn & "$" & lastRow​

to ensure that the pint are a encompassed all section then checked for automatic horizontal page breaks.

Take Care!