Macro to insert page breaks please in Excel

Bowraven

New Member
Joined
Feb 7, 2017
Messages
30
Hello, I'm looking for some help with a macro please.

At present the macro performs the following:

1. Unprotects the active worksheet.
2. Filters the active worksheet to hide blank lines.
3. re-protects the active worksheet.
4. Resets the page breaks on the active worksheet so there are no inserted breaks.
5. Inserts breaks on the active worksheet using column 'V' from row 1 to row 65536. Page breaks are inserted when there is an increase in the number in this column.

What I'd like to do is to add to the macro so that it also inserts breaks across the page too using row 400 from column A to Column Z.

Is this possible?

If so please will you help with the code in the macro below.

I'd appreciate it if you could re-write the whole macro to do this process:


Sub NoZeroLinesForPrint()
'
' NoZeroLinesForPrint Macro
' Macro recorded 09/09/2009 by russell
'
' Keyboard Shortcut: Ctrl+n
'



ActiveSheet.Unprotect
Range("s1").Select
Selection.AutoFilter Field:=19, Criteria1:="<>0", Operator:=xlAnd
Range("N180").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Range("B7").Select

ActiveSheet.ResetAllPageBreaks
Dim rngMyRange As Range, rngCell As Range

With ActiveSheet
Set rngMyRange = .Range(.Range("v1"), .Range("v65536").End(xlUp))
'the range to work with
For Each rngCell In rngMyRange
'loop through the range
If rngCell.Value <> rngCell.Offset(1, 0).Value Then
.HPageBreaks.Add Before:=rngCell.Offset(1, 0)
End If
Next
End With



End Sub


Thank you for your help.

Russell
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hello


Code:
Sub NoZeroLinesForPrint()
Dim MyRange As Range, Cell As Range, sh As Worksheet
Const R = 150                                           ' desired row
Set sh = ActiveSheet
sh.Unprotect
[a:s].AutoFilter Field:=19, Criteria1:="<>0", Operator:=xlAnd
sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=1, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
sh.ResetAllPageBreaks
With sh
    Set MyRange = .Range(.Range("v1"), .[v65536].End(xlUp))
    For Each Cell In MyRange
        If Cell <> Cell.Offset(1) Then .HPageBreaks.Add Before:=Cell.Offset(1)
    Next
    Set MyRange = .Range(.Cells(R, 1), .Cells(R, 26))       ' from A to Z
    For Each Cell In MyRange
        If Cell <> Cell.Offset(, 1) Then .VPageBreaks.Add Cell.Offset(, 1)
    Next
End With
End Sub
 
Upvote 0
Hi Worf,

Thank you for your reply and for providing this macro.

I have tried this, but it's not working as it is.

On the horizontal page breaks I know my information to set the breaks is in column v - i.e. "v1" to "v65536"

But not sure about the vertical breaks - where is the macro looking for the changing information please. I originally had this along row 400.

What I'm trying to work out is how and which row to change to get the macro to work and to insert the page breaks at the right places.

For example, in column "V" to get the horizontal breaks to work the numbers increase by one when the data changes, so it automatically enters a page break. But it's not so obvious from the macro how it works for the vertical breaks.

Thank you, Russell
 
Upvote 0
Hi Russell


o I redefined “My Range “to a horizontal range where the changing information is. Alter the constant “R” from 150 to 400 to get that row, see the comments on the code.
o My example works with A150:Z150 for the vertical breaks, using the same logic employed for the horizontal ones.
 
Upvote 0
Hi Worf,

Thank you for your quick reply and for explaining the code - it works perfectly now.

Thank you for your help.

Regards, Russell
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top