Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing all manual page breaks (VBA Excel 2000)

    This ought to be a no brainer, but what they say in the OH doesn't seem to work.

    How can I use VBA to remove all manual page breaks in a worksheet?

    Thanks!!
    Troy

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    The manual method works for me. See graphic. The VBA is:

    ActiveSheet.ResetAllPageBreaks
    Attached Images Attached Images
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    That seems to do more than just remove manual page breaks. One thing I notice is that it changes some of the page setup, such as the scaling.

    Thus, it causes more problems than it solves.

    Any other thoughts?

    Thanks!!
    Troy

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    Hi Troy,

    I just tried:

    Sub KillPB()
    Cells.PageBreak = xlPageBreakNone
    End Sub

    as per the 2K on-line help, and it worked fine, removing page breaks that had been set on both rows and columns.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    That works fine if I'm in Normal view, but try running that while in Page Break View.

    I get:

    Run-time error '1004'

    Application-defined or object-defined error


    Any ideas why?

    Troy

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    The error is probably caused by the page layout changing while the macro is executing. You can overcome that by temporarily switching back to normal view:

    Sub KillPB()
    ActiveWindow.View = xlNormalView
    Cells.PageBreak = xlPageBreakNone
    ActiveWindow.View = xlPageBreakPreview
    End Sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    Great idea!!

    Thanks!!
    Troy

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    John,

    This has nothing to do with this post but how did you paste what appears to be a screen dump into your reply?

    Thanks,
    John

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Removing all manual page breaks (VBA Excel 2000)

    Leif tells all in his <!post=Screen Capture StarPost,158453>Screen Capture StarPost<!/post> in Lounge Matters, except that I use standard MS Paint (instead of Photo Editor) to paste the capture, and free IrfanView to edit. HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •