HPageBreaks.Location Error

[JessP]

Hello,

I have a piece of code designed to place horizontal page breaks so the heading for a group of data isn't by itself at the bottom of a page. However, it throws a run-time error 9 - subscript out of range.

This appears to be a known issue, but Microsoft's solution, to select the last cell first, doesn't seem to be working, unless I'm misunderstanding what they said. Has anyone experienced this or have any ideas about how to fix it?

Disclaimer: I'm aware there are a lot of "selects" in my code - they're from attempts at debugging and haven't been removed yet.

Dim rg1 as Range
Dim scrollRow as Long
Dim scrollColumn as Long
Dim rgPrintArea as Range
Dim lastrow as Range, lastcol as Range, LastCell as Range
Dim i as Long, j as Long

On Error GoTo 0
Application.EnableEvents = False
With Sheets("FoE")
    Set rg1 = ActiveCell
    scrollRow = ActiveWindow.scrollRow
    scrollColumn = ActiveWindow.scrollColumn
    Set rgPrintArea = Intersect(.Range(.PageSetup.PrintArea), .UsedRange)
    
    .ResetAllPageBreaks
    .PageSetup.Zoom = False
    .PageSetup.FitToPagesWide = 1
    .PageSetup.FitToPagesTall = False
    ActiveWindow.View = xlPageBreakPreview
    
    .DisplayAutomaticPageBreaks = True
    Set lastrow = rgPrintArea.Find(What:="*", After:=rgPrintArea.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious)
    Set lastcol = rgPrintArea.Find(What:="*", After:=rgPrintArea.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious)
    Set LastCell = Cells(lastrow.Row, lastcol.Column)
    LastCell.Select
    
    If .HPageBreaks.Count > 0 Then
        Do
            i = i + 1
            'The following line is where it throws the subscript out of range error:
            .HPageBreaks(i).Location.EntireRow.Select
            j = Selection.Row
            If .Cells(j - 1, 2).Interior.Color = RGB(191, 191, 191) Then
                .Cells(j - 1, 2).EntireRow.Select
                j = Selection.Row
                If .Cells(j - 1, 2).Interior.Color = RGB(191, 191, 191) Then
                    .Cells(j - 1, 2).EntireRow.Select
                    j = Selection.Row
                End If
            End If
            LastCell.Select
            If j <> .HPageBreaks(i).Location.Row Then
                Set .HPageBreaks(i).Location = .Cells(j, 2)
            End If
            If i >= .HPageBreaks.Count Then Exit Do
        Loop
    End If
    
    rg1.Select
    ActiveWindow.scrollRow = scrollRow
    ActiveWindow.scrollColumn = scrollColumn
    ActiveWindow.View = xlNormalView
End With
Application.EnableEvents = True

 

[John_w]

I came across this problem, but wasn't aware it was a documented issue. My solution was also to Select the last cell in the used range, however I found that that wasn't always needed and the code worked without selecting the last cell.

My code - https://www.mrexcel.com/forum/excel...ws-bottom-border-post4759099.html#post4759099 - only selects the last cell once, before it loops through HPageBreaks.

Also, depending on where your code is located (in a sheet or standard module) and the active sheet, the following line may not be referencing the "FoE" sheet:

    Set LastCell = Cells(lastrow.Row, lastcol.Column)

It needs a "." in front of Cells.

 

[JessP]

My code - https://www.mrexcel.com/forum/excel...ws-bottom-border-post4759099.html#post4759099 - only selects the last cell once, before it loops through HPageBreaks.

Also, depending on where your code is located (in a sheet or standard module) and the active sheet, the following line may not be referencing the "FoE" sheet:

    Set LastCell = Cells(lastrow.Row, lastcol.Column)

It needs a "." in front of Cells.

I added the "." and made sure it was referencing the correct sheet, but to no avail. So I tried modifying your code, but with my changes it doesn't seem to adjust the page breaks at all, and I'm not sure why, although I'm a relative novice to VBA. Here's what I came up with - do you know where I might have messed it up?

Dim ws As Worksheet
Dim saveActiveCell As Range
Dim lastRow As Long, r As Long
Dim pb As HPageBreak
Set ws = ActiveWorkbook.Sheets("FoE")
Set saveActiveCell = ActiveCell
With ws
    lastRow = .UsedRange.Rows.Count - .UsedRange.Row + 1
    .Cells(lastRow, "B").Select
    For Each pb In .HPageBreaks
        Debug.Print pb.Location.Address
        If .Cells(pb.Location.Row - 1, "B").Interior.Color = RGB(191, 191, 191) Then
            r = pb.Location.Row
            Do
                r = r - 1
            Loop Until r = 1 Or .Cells(r, "B").Interior.Color <> RGB(191, 191, 191)
            
            If .Cells(r, "B").Interior.Color <> RGB(191, 191, 191) Then
                If pb.Type = xlPageBreakAutomatic Then pb.Type = xlPageBreakManual
                pb.Delete
                .HPageBreaks.Add before:=.Cells(r + 1, "B")
            End If
        End If
    Next
End With
saveActiveCell.Select

 

[John_w]

Your new code works for me (Excel 2016), on some test data - heading rows at 1, 13, 35, 50, 64, 85, 100, 111 and the last data row is 130. The automatic page break at row 51 is moved to 50 and the one at 101 is moved to 100.

Which rows are your headings on, and what is the last data row?

 

[JessP]

Well that's odd - I tried running it again this morning and it works. Don't know what happened yesterday. Many thanks for the help.

 

[JessP]

Sorry to double post - after further testing it's not working quite as expected.

Your new code works for me (Excel 2016), on some test data - heading rows at 1, 13, 35, 50, 64, 85, 100, 111 and the last data row is 130. The automatic page break at row 51 is moved to 50 and the one at 101 is moved to 100.

Which rows are your headings on, and what is the last data row?

Rows 1:2 repeat at the top of each page. Rows 3, 13, 15, 17, 23, and 46 contain headers. Row 58 is the last row containing data. However, various rows are hidden and they aren't all the same height.

When I un-hid all rows through 14, hid 15 and 16, and un-hid 17:20,22:25,27,29:32 (I think that should be enough to recreate, but let me know if you want the rest), through row 12 was on the first page, which is good, but then only 13:14 were on the second page, while the next group (17:20,22) would easily fit and should be on that page as well. Instead, 17:20,22:25,27,29:30 were on the third page. I don't quite understand how it's going about splitting the data - it seems almost random, although I'm sure there's a logical explanation.

 

[JessP]

Never mind, I figured out a solution - added ".ResetAllPageBreaks" after "With ws".

 

[jolivanes]

https://support.microsoft.com/en-gb/kb/210663
There is a bug in Excel for counting PageBreaks.
There are two ways to get around it.
For #1 , read the above microsoft kb bulletin
For #2 , insert

ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView

 

[JessP]

https://support.microsoft.com/en-gb/kb/210663
There is a bug in Excel for counting PageBreaks.
There are two ways to get around it.
For #1 , read the above microsoft kb bulletin
For #2 , insert

ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView

Yes, if you look at my original post, I linked to the Microsoft page you reference. My original code (which I posted above) tried remedying the problem with xlPageBreakPreview and selecting the last cell first, neither of which work. Thanks for trying to help, but I created a solution that works with John's code as a starting point. If you can find a way to make my original code work, I would be curious to see it, but it's not necessary.

 

[jolivanes]

I use the "ActiveWindow" suggestion in combination with "Reset AllPagebreaks" and have no problems but whatever works for you, you should use