Deleting Page Breaks 2

[cwinnyk]

What's wrong with my code?

For Each pagebreak In Application.ActiveSheet.HPageBreaks
If pagebreak.Type = xlPageBreakManual Then pagebreak.Delete
Next pagebreak

I am trying to delete all the manual page breaks that I have created during the execution of the macro. However, I get a 1004 Application defined or object defined error.

I don't get it!

 

[Zathras]

"pagebreak" is the name of a property and Excel is getting confused as to what you intend. Try this instead:
[blue]

Sub DeletePageBreaks()
Dim oPB As HPageBreak
  For Each oPB In Application.ActiveSheet.HPageBreaks
    If oPB.Type = xlPageBreakManual Then oPB.Delete
  Next oPB
End Sub

[/color]
Or simply do this:
[blue]

   ActiveSheet.ResetAllPageBreaks

[/color]

 

[sfvb]

The problem is with the .Delete forcing a reindexing.

Example: Say there are 5 pagebreaks and all 5 are manual pagebreaks, and they are indexed as follows:
pb1 = HPageBreak(1)
pb2 = HPageBreak(2)
pb3 = HPageBreak(3)
pb4 = HPageBreak(4)
pb5 = HPageBreak(5)

The For Each...Next sets up a loop of 5 iterations.
The 1st iteration says "If the HPageBreak(1) is a manual pagebreak then delete it". The application deletes the pagebreak, but now the problem surfaces.
The .Delete forces a reindexing of the HPageBreak collection, so instead of leaving you with:
pb2 = HPageBreak(2)
pb3 = HPageBreak(3)
pb4 = HPageBreak(4)
pb5 = HPageBreak(5)

you actually get:
pb2 = HPageBreak(1)
pb3 = HPageBreak(2)
pb4 = HPageBreak(3)
pb5 = HPageBreak(4)

The 2nd iteration says "If the HPageBreak(2) is a manual pagebreak then delete it". The application deletes
HPageBreak(2), (which as you'll notice is "pb3&quot. Then it reindexes again, leaving you with:
pb2 = HPageBreak(1)
pb4 = HPageBreak(2)
pb5 = HPageBreak(3)


The 3rd iteration says "If the HPageBreak(3) is a manual pagebreak then delete it". The application deletes
HPageBreak(3), (which is "pb5&quot. Then it reindexes again, leaving you with:
pb2 = HPageBreak(1)
pb4 = HPageBreak(2)

The 4th iteration says "If the HPageBreak(4) is a manual pagebreak then delete it". The error actually occurs on the comparison operation of the If statement, because there is no HPageBreak(4).

To solve the problem, delete in reverse order

Dim idx           As Integer

For idx = Application.ActiveSheet.HPageBreaks.Count To 1 Step -1
   If ActiveSheet.HPageBreaks(idx).Type = xlPageBreakManual Then
      Application.ActiveSheet.HPageBreaks(idx).Delete
   End If
Next idx

 

[Zathras]

Interesting... The [blue]

 for each oPB

[/color] technique worked ok in Excel 97 but fails in Excel 2K.

The[blue]

 ActiveSheet.ResetAllPageBreaks

[/color]
technique works in both versions.

 

[cwinnyk]

Thanks guys. I decided to go with

ActiveSheet.ResetAllPageBreaks

sfvb, thats really interesting how Excel handles the indexing of the page breaks. That's exactly what my problem was. Thanks for teaching me something new.