In Excel 2007, why does dragging a manual horizontal page break down cause the previous automatic one to change to manual..?

3
votes

For the past few days I've been working on some Excel 2007 VBA code that manages the page breaks in a complex worksheet. After much frustration, I just solved an issue of "jumping page breaks" that was driving me nuts, and I made the following discovery which leads me to the question of, "Why?"...

When in Page Break View, and the mouse is used to drag a manual horizontal page break further down the sheet, it causes the previous automatic page break to switch to manual. This doesn't happen if the page break is dragged up; it only happens when the page break is dragged down.

Can someone explain the mechanism and reason for this..? It seems very non-intuitive and caused me some serious frustration until I figured it out a little while ago. I can understand that moving a page break would affect those further down the sheet, but why the one above it..?

Here's a bit of VBA that will set up a demo.

  1. Run it.
  2. Drag the manual page break up (the one above row 60).
  3. Nothing happens.
  4. Then drag it down.
  5. The automatic page break further up the sheet switches to manual.
  6. Why?

The test can be repeated if the newly-switched manual page break is deleted, after which it will revert to automatic. Then drag the other one and watch it switch again.

Public Sub PageBreakTest()
    Dim x As Long
    Dim y As Long
    For y = 1 To 75
        For x = 1 To 20
            Cells(y, x).Value = Cells(y, x).Address(0, 0)
        Next
    Next
    Rows("60:60").PageBreak = xlPageBreakManual
    ActiveWindow.View = xlPageBreakPreview
    Range(Cells(1, 1), Cells(75, 30)).Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End Sub
1
excelvba

1 Answers

3
votes

This is by design (also in my Excel 2016).

If you drag the end of a page upwards, you typically want to shorten that page. This works, if the previous page break remains automatic.

If you drag the page break of its end down, you typically want to enlarge the number of printed rows on that page. If the previous page break remains automatic, you would suddenly have an additional page break with a page size of just one row. To prevents that, the previous break is set to automatic.

Same happens with the vertical page breaks: If you move one of them to the left (closer to the previous automatic one, smaller page), then the previous stays automatic. If you move it just 1 column to the right, the previous one gets a manual one also.

Just an addition: If you adapt page sizes (amount of row numbers) manually by adding page breaks, then you typically start from page 1 to the end of your worksheet. During that, all remaining page breaks below your current page still stay automatic until you touch them.