Insert page breaks every 50 rows but do not include hidden rows

[Guest]

Hi there,

Is there a method to put page breaks in the used range every 50 rows but
exclude any hidden rows in the count of rows.

Example: a sheet with 200 rows of data and row 25 to 39 and 50 to 74 are
hidden. The first page break should then appear at row 90 and the second one
at row 140 etc.

Any help much appreciated.

 

[Guest]

Sub pformt()
k = 0
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
For n = nFirstRow To nLastRow
If Cells(n, "A").EntireRow.Hidden Then
Else
k = k + 1
End If
If k = 51 Then
k = 1
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(n, "A")
End If
Next
End Sub

 

[Incidental]

Hi There

I'm not sure exactly what it is you want to do with the sheet but it
would sound like you want to print it??? if so you could try
something like the following to process it for you what it does is
creates a new sheet then copies all the visible rows to it and set
your page breaks from there.

Private Sub CommandButton1_Click()
With Sheets.Add
.Name = "Filtered Sheet"
Sheets("sheet1").
[a1:a200].SpecialCells(xlCellTypeVisible).EntireRow.Copy
Sheets("Filtered Sheet").Range("A1").PasteSpecial
End With
Sheets("Filtered Sheet").Rows(50).PageBreak = xlPageBreakManual
Sheets("Filtered Sheet").Rows(100).PageBreak =
xlPageBreakManual
Sheets("Filtered Sheet").Rows(150).PageBreak = xlPageBreakManual
End Sub

This might not be what you are looking for but it may be of some help
to you.

Thanks

S

 

[Dave Peterson]

Maybe something like this:

Option Explicit
Sub testme()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
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 50 = 1 Then
.HPageBreaks.Add before:=myCell
End If
End If
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thank you so much!
--
Regards,

Martin

Sub pformt()
k = 0
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
For n = nFirstRow To nLastRow
If Cells(n, "A").EntireRow.Hidden Then
Else
k = k + 1
End If
If k = 51 Then
k = 1
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(n, "A")
End If
Next
End Sub

 

[Guest]

Thank you so much!
--
Regards,

Martin

Maybe something like this:

Option Explicit
Sub testme()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
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 50 = 1 Then
.HPageBreaks.Add before:=myCell
End If
End If
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm