I found this code in the forum for applying a page break at every 50 rows of
data and I changed it to 17 rows. The problem is, is that it applies not only
a horizontal but also a verticle page break. The other issue is that it
applies the horizontal one at row 21 and not at 17. Why??

Sub setPage()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim searchFor As String

searchFor = FrmCreate.CbxDept.Text
Set wks = Worksheets(searchFor)
With wks
.ResetAllPageBreaks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No visible cells!"
Exit Sub
End If

iRow = 0
For Each myCell In myRng.Cells
iRow = iRow + 1
If iRow 1 Then
If iRow Mod 17 = 1 Then
.HPageBreaks.Add befo=myCell
Call FormatHeaders
End If
End If
Next myCell
End With

End Sub

I think you're seeing a natural vertical page break--the code isn't adding any.

And this code was used with data that was filtered--some of the rows were
hidden. The original poster only wanted ## number of visible rows per printed
page.

I'm guessing that you have some hidden rows.

Mekinnik wrote:

I found this code in the forum for applying a page break at every 50 rows of
data and I changed it to 17 rows. The problem is, is that it applies not only
a horizontal but also a verticle page break. The other issue is that it
applies the horizontal one at row 21 and not at 17. Why??

Sub setPage()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim searchFor As String

searchFor = FrmCreate.CbxDept.Text
Set wks = Worksheets(searchFor)
With wks
.ResetAllPageBreaks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No visible cells!"
Exit Sub
End If

iRow = 0
For Each myCell In myRng.Cells
iRow = iRow + 1
If iRow 1 Then
If iRow Mod 17 = 1 Then
.HPageBreaks.Add befo=myCell
Call FormatHeaders
End If
End If
Next myCell
End With

End Sub

--

Dave Peterson