M
Mekinnik
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 before:=myCell
Call FormatHeaders
End If
End If
Next myCell
End With
End Sub
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 before:=myCell
Call FormatHeaders
End If
End If
Next myCell
End With
End Sub