Removing ALL Page Breaks with VBA

Hi Tom,

Try this:

Sub RemovePageBreaks()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.PageSetup.PrintArea = ""
Next ws

End Sub


Jim

Public Sub x()
  Dim h As HPageBreak
  Dim v As VPageBreak

  While Sheet1.HPageBreaks.Count > 1
    Set h = Sheet1.HPageBreaks(Sheet1.HPageBreaks.Count - 1)
    h.DragOff Direction:=xlDown, RegionIndex:=1
  Wend

  While Sheet1.VPageBreaks.Count > 1
    Set v = Sheet1.VPageBreaks(Sheet1.VPageBreaks.Count - 1)
    v.DragOff Direction:=xlRight, RegionIndex:=1
  Wend

End Sub

Slight addition to Jim's:

For Each ws In ActiveWorkbook.Worksheets
    ws.PageSetup.PrintArea = ""
    ws.ResetAllPageBreaks
Next ws

Matt

AngelIII,

This is what I am trying....

    Dim h As HPageBreak
    Dim v As VPageBreak
   
    For x = 1 To 14
        Sheets(ActiveWorkbook.Sheets(x).Name).Select

        While Sheets(ActiveWorkbook.Sheets(x).Name).HPageBreaks.Count > 1
            Set h = Sheets(ActiveWorkbook.Sheets(x).Name).HPageBreaks(Sheets(ActiveWorkbook.Sheets(x).Name).HPageBreaks.Count - 1)
            HPageBreak.DragOff Direction:=xlDown, RegionIndex:=1
        Wend
       
        While Sheets(ActiveWorkbook.Sheets(x).Name).VPageBreaks.Count > 1
            Set v = Sheets(ActiveWorkbook.Sheets(x).Name).VPageBreaks(Sheets(ActiveWorkbook.Sheets(x).Name).VPageBreaks.Count - 1)
            v.DragOff Direction:=xlRight, RegionIndex:=1
        Wend
    Next

and I get the following error....

Object required... and I'm getting it on this line....

HPageBreak.DragOff Direction:=xlDown, RegionIndex:=1

Replace "HPageBreak" with "h" you should be good to go with that code.

Also, if you think you may have any pages with the "set to __ pages wide by __ pages tall", add this to the worksheet loop:

    ws.PageSetup.Zoom = 100

Matt

When I changed "HPageBreak" to "h" I get the following error now....

Application-Defined or Object-Defined Error

You're getting that error when you changed hpagebreak to h in the line:

  h.DragOff direction:=xlDown, RegionIndex:=1

?

Yep, not sure why and when I tried

Sub RemovePageBreaks()
 Dim WS As Worksheet
 For Each WS In ActiveWorkbook.Worksheets
  WS.PageSetup.PrintArea = ""
  WS.ResetAllPageBreaks
  WS.PageSetup.Zoom = 100
 Next WS
End Sub

It removed all amual page breaks but not the automatic ones... they all need to be dragged to the right

Perhaps I'm misunderstanding the question then, Thomas, I'm sorry.  The only ways I know how to 'remove' the automatic page breaks are either with:

 With ActiveSheet.PageSetup
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
 End With

or:

 ActiveSheet.DisplayAutomaticPageBreaks = False

If your sheet is set to print with specific margins, no defined print area, and no manual page breaks, then the automatic ones will be there, as far as I know with no other way to get rid of them.  Angel's code gives me the same error you get when trying to move the automatic page breaks (assuming they're displayed), since excel can't force more onto a page than what will fit.  I'm going to stay subscribed to the question as I'm curious to see how else it can be done.

mvidas,

Tried this and it works great......

    Dim WS As Worksheet
   
    For Each WS In ActiveWorkbook.Worksheets
        Sheets(WS.Name).Select
        ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.DisplayAutomaticPageBreaks = False
        With ActiveSheet.PageSetup
         .Zoom = False
         .FitToPagesWide = 1
         .FitToPagesTall = 1
        End With
        WS.PageSetup.PrintArea = ""
        WS.ResetAllPageBreaks
        ActiveWindow.View = xlNormalView
    Next WS

Removes everything, then I go back through each worksheet and set everything up the way I want using this code...

Function FormatRegionalDetail()
    Dim nRow As Long
    Dim RegionalDetail(5) As Variant
    Dim RegionalDetailEndRow As Integer
    Dim WorkSheetHeading As String
   
    RegionalDetail(1) = "CARTN Detail"
    RegionalDetail(2) = "CTX Detail"
    RegionalDetail(3) = "FL Detail"
    RegionalDetail(4) = "GAAL Detail"
    RegionalDetail(5) = "HGC Detail"
    RegionalDetailEndRow = 5

    For x = 1 To RegionalDetailEndRow
        Sheets(RegionalDetail(x)).Cells.PageBreak = xlPageBreakNone
        Sheets(RegionalDetail(x)).Select
       
        WorkSheetHeading = RegionalDetail(x) & " Worksheet"
       
        Cells.Select
        ActiveSheet.PageSetup.PrintArea = ""

        'Last row in column A containing data
        nRow = Cells(65536, 1).End(xlUp).Row
       
        Range("A1:AB" & nRow).Select
        ActiveSheet.PageSetup.PrintArea = "$A$1:$AB$" & nRow
        Columns("O:O").Select
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
        With ActiveSheet.PageSetup
            .PrintTitleRows = "$3:$5"
            .PrintTitleColumns = "$A:$B"
        End With
        ActiveSheet.PageSetup.PrintArea = "$A$1:$AB$" & nRow
        With ActiveSheet.PageSetup
            .LeftHeader = WorkSheetHeading
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = "&D"
            .CenterFooter = "Confidential - for VZW Internal Use Only"
            .RightFooter = "Page  &P of &N"
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlOverThenDown
            .BlackAndWhite = False
            .Zoom = 70
        End With
    Next
    Range("A6").Select
End Function

mdivas, I'm splitting the points between you and Jim since both helped out greatly.....