Remove manual page break from an excel sheet

[Per9922]

Hello,

I would like to remove all manual page break from a sheet .... some help please ... I have the code below, but I got an error if I run the code with no manual page(I have two auto) break here:
ActiveSheet.HPageBreaks(2).Type ? Saying error in index ?

Number = ActiveSheet.HPageBreaks.Count
For i = 1 To Number
If ActiveSheet.HPageBreaks(i).Type = xlPageBreakManual Then
NumberManual = NumberManual + 1
End If
Next

Number = Application.ActiveSheet.HPageBreaks.Count
For i = 1 To Number
If ActiveSheet.HPageBreaks(i).Type = xlPageBreakManual Then
Application.ActiveSheet.HPageBreaks(i).Delete
Number = Application.ActiveSheet.HPageBreaks.Count
i = 0
NumberManualRemoved = NumberManualRemoved + 1
If (NumberManualRemoved = NumberManual) Then Exit Sub
End If
Next

 

[Gavona]

if the error only occurs when there are no manual pagebreaks then either test for the specific condition or use a more general error handler:

On error goto mylabel
If ActiveSheet.HPageBreaks(i).Type = xlPageBreakManual 
On error goto 0
.
.(existing code)
.
mylabel:
Next

If ActiveSheet.HPageBreaks(i).Type <> xlPageBreakManual goto mylabel 
.
.(existing code)
.
mylabel:
Next

Gavin

 

[Per9922]

THanks!

 

[SkipVought]

hi,

No need to code for the absence of page breaks...

    Dim hpb As HPageBreak, vpb As VPageBreak
    
    With ActiveSheet
        For Each hpb In .HPageBreaks
            hpb.Delete
        Next
        
        
        For Each vpb In .VPageBreaks
            vpb.Delete
        Next
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}