Macro to Remove Page Breaks

[vmottert]

I am working on a simple macro to let users remove unwanted page breaks with a keyboard shortcut. Here is what I have.

Sub RemovePageBreaks()
ActiveWindow.SelectedSheets.HPageBreaks.Delete Before:=ActiveCell
End Sub

Seems like it should work but I get the error message: "Object doesn't support this property or method."

Any help appreciated.

Vernon

 

[vmottert]

Trying once more - Vernon

 

[kgkev]

Bit of a botch - doesn't use VBA

Right click the tool bar (any)

Click Customize..

Under the command tabs - click insert - then drag PAGE BREAK onto a task bar.


Now ALT+B will remove the above page break. or add a new one

 

[Kenneth Hobson]

To delete all on the activesheet:

Sub RemovePageBreaks()
  Dim pb As HPageBreaks
  For Each pb In ActiveSheet.HPageBreaks
    pb.Delete
  Next pb
End Sub

 

[vmottert]

kgkev,
Yep, that works. Little work to do and undo as needed, though. And harder to communicate to users than "Press Ctrl + Shift + R". Might have to do though.
Thanks,
Vernon

 

[vmottert]

Kenneth,
Thanks. But this is for use on a report that the user may want an easy way to delete only selected page breaks to control the number of pages in the print job. My idea was for the user to place the cursor under a page break and press [ Ctrl + Shift + R ] to remove a page break. It would be quicker to do this from the keyboard for a lot of them than with the mouse.
Vernon

 

[kgkev]

Alternatively to make my method easier you could use

alt+IB

ie hold ALT then type I B

 

[vmottert]

kgkev,
You're right about the Alt+IB. It is about as easy as [ Ctrl + Shift + R ] and doesn't require a macro. Sometimes I think too much
Thanks twice,
Vernon