Page break issues
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 |
Page break issues
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 |
All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com