Remove or Adjust PageBreaks

11-26-2004, 06:23 PM

I have a worksheet which I clear, then use the Copy method to copy from other sheets and paste into this blank one.

I set the PageSetup.FitToPageWide = 1, to set the page area, but i'm left with a vertical pagebreak, which I don't need.

I can't remove it. I've tried the DragOff, but that don't work. As far as I can see the VPageBreak object has a Delete method. But, I can't get that working either.

Any help?

11-26-2004, 10:06 PM
This should get rid of all vertical page breaks:With ActiveSheet
For i = 1 To .VPageBreaks.Count
Next i
End WithReplace the 'Activesheet' with your sheet object if needed and you can replace the 'V' with an 'H' for the horizontal pagebreaks.

11-27-2004, 07:12 AM
I get an "Application defined or object defined error" message on this line :



11-27-2004, 09:38 AM
What does .VPageBreaks.Count return?

11-27-2004, 12:12 PM
Even if there are no pagebreaks to delete the code should run error free. Could you post the full code you're using?

11-29-2004, 03:18 AM
.VPageBreaks.Count returns 1.

The full code for this part is as follows :

With Sheet8
.Cells.Font.Size = 8
Selection.ColumnWidth = 0.5
With .PageSetup
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = False
.CenterHorizontally = True
.CenterVertically = False
End With
For i = 1 To .VPageBreaks.Count
Next i
End With

All help appreciated on this.

11-29-2004, 10:01 AM
Hi loopy,

Just to verify, are you saying that if you copy and paste this exact code as the only code within your command button, it gives you an error?

With Sheet8
For i = 1 To .VPageBreaks.Count
Next i
End With

If so, are you sure that Sheet8 is the sheet you're working with??
- Note that Sheet8 is a code name and doesn't necessarily have to be matched to the name that it may have been customized to.

Just as a test, try the code like this:

With Worksheets("Sheet8")
MsgBox .VPageBreaks.Count' just to verify page break is being detected
For i = 1 To .VPageBreaks.Count
Next i
End With

11-29-2004, 11:01 AM
I am absolutely sure the script is working with the correct worksheet.

Setting any other property from within the With block works as expected.

.VPageBreaks.Count returns 1, and there is only 1 page break within that sheet.

It just won't go away.

11-29-2004, 12:54 PM
I've tried this in XP and I'm finding I get the same problem, even though intellisense shows the Delete method should be available for both the Collection and VPageBreak object (called by iterating through the collection).

Nothing on Google about this. Might be a service pack thing?.. :-\

11-29-2004, 04:38 PM
Are other VPageBreak Methods working, is it a VPageBreak thing or just it's Delete Method?
loopy: could you try creating a pagebreak in a fresh workbook and see if the code still freaks? See if it's at the Application level or a corrupted workbook.
Anybody know which library contains this method?

11-30-2004, 12:31 AM
Hi Guys,

When I tried the code yesterday, it worked fine multiple times, but Now the same thing is happening to me, in Excel 2000
Well, sort of, maybe...

If I manualy insert a page break at column(2 thru 8)
and then I run the code given below,
the MsgBox first reports(1)page break, then removes it,
and places a new page break at Column(9)

Private Sub CommandButton1_Click()
'On Error Resume Next
With Sheet1
MsgBox .VPageBreaks.Count
For i = 1 To .VPageBreaks.Count
Next i
End With
End Sub

Then if I move the CommandButton so its either straddling or to the right of the Column(9) automatic page break, and run the code a 2nd time, the MsgBox reports(1)page break,
then it throws an Error. (I tried this multiple times, with the same result.)
Weirder still is that if I move the button back inside the Column(9) page break, it for the most part, no longer throws the error; *but it still did fail a couple of times, out of about 20 tries. , And, in one workbook that I tried it, it continually always threw the Error, no matter where I locate the CommandButton.

*** :)
The good news is: that I solved the issue; (in my workbook, at least), by selecting the Format Control option, of the CommandButton, and unchecking the print object CheckBox.

Edit: I imagine the routine could be made to both deselect and reselect the ability to print the Button, to also circumvent the problem, but the code I tried was inconsistent, so I removed it and am not going to debug it, unless it's requested by Loopy.)


11-30-2004, 04:49 PM
and places a new page break at Column(i)Whanever you access the PageBreak objects in VBA Excel reacts like you went into Print Preview mode then returned and puts those dotted lines at (in this case) the default pagebreak location. Apparently this isn't a the same as a manually inserted pagebreak. From your observations I'm guessing it doesn't become a true pagebreak until there is something on a page 2 to print. I was able to recreate your error in Excel'97. Also, when I manually inserted a pagebreak at this default location, the code reported 1 pagebreak then removed it succcessfully. A second run (with no manual pagebreaks) resulted in the error again.

12-21-2010, 01:01 PM
Using Excel 2007. This thread gave me the clue to fix my problem... needed to remove all vertical page breaks. After reading this thread figured out that i needed to switch to page break preview, run the code, then switch back to normal view. This works like a charm to remove all vertical page breaks.

If ActiveSheet.VPageBreaks.Count > 0 Then
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
End If

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum