Macro to insert page breaks please in Excel

[Bowraven]

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

 

[Worf]

Hello

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

 

[Bowraven]

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

 

[Worf]

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.

 

[Bowraven]

Hi Worf,

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

Thank you for your help.

Regards, Russell

 

[Worf]

You are welcome and thanks for the feedback.