Page breaks, search manual PageBreaks & time error 9: “Subscript out of Range”

[Deverti]

I wonder if there is a way to only select page breaks which have been manually added while ignoring any automatically generated page breaks. that would be great.

concerning the "out of range" error, apparently it s a well known occurance and selecting/loading the end of the sheet fixes this but it s quite the makeshift solution i guess. is there a better way to deal with this?

Sub hPage()

ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select     'error 9 fix

Dim hPage As HPageBreak
        For Each hPage In ActiveSheet.HPageBreaks
            hPage.Location.Select
        Next hPage
End Sub

 

[Anthony47]

Your message is a little bit cryptic... If one of the questions is "please help me ti identify the page breaks that have been set by the user" then let's first test the following macro:

Sub CheckHB()
Dim HPB, PSUTop As Single, I As Long, J As Long
Dim HBPrePost(), PreArr, PostArr, bGuess As String
'
Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
PSUTop = Worksheets(1).PageSetup.TopMargin
ReDim HBPrePost(1 To 2, 1 To (2 * ActiveSheet.HPageBreaks.Count))
'
For I = 1 To 2
ActiveSheet.PageSetup.TopMargin = PSUTop * I
'    II = 0
    For J = 1 To ActiveSheet.HPageBreaks.Count
        HBPrePost(I, J) = ActiveSheet.HPageBreaks(J).Location.Row
    Next J
Next I
ActiveSheet.PageSetup.TopMargin = PSUTop
'
ActiveWindow.View = xlNormalView
PreArr = Application.WorksheetFunction.Index(HBPrePost, 1, 0)
PostArr = Application.WorksheetFunction.Index(HBPrePost, 2, 0)
bGuess = " "
For I = LBound(PostArr) To UBound(PostArr)
    If Not IsError(Application.Match(PostArr(I), PreArr, False)) Then
        bGuess = bGuess & PostArr(I) & ", "
    End If
Next I
Application.ScreenUpdating = True
MsgBox ("Best guess: forced line break(s) at line(s) " & Left(bGuess, Len(bGuess) - 2))
End Sub

It try to identify the forced pagebreaks by momentarily changing and the restoring the page set up parametres; at the end of fthe process a message will advice which seems to be the forced breaks.

Test if the results are right or wrong, then we will decide what can be done as a second step.

Bye

 

[Deverti]

Oh god, i already assumed my question to have been swallowed by the masses and I found a workaround that works for me in the meantime. Definitely didnt expect such an elaborate code.

I was hoping one could merely add an additional criteria to define "Dim hPage As HPageBreak" as a page break which has been set by the user.

My last page break is in the higher 4 digits, slight overkill for my case I suppose. Does exactly what i asked for tho, so thanks a bunch for sharing ?

 

[Anthony47]

Glad it worked for you
Bye