print every 44 lines (including blank lines) per page

Hi.

I'm trying to print every 44 lines (including blank lines) per page.

Sub TryPrint()
' I need print 44 rows per page
For i = 1 To 19766 Step 44
    ActiveSheet.HPageBreaks.Add Before:=Cells(i + 1, 1)
Next
End Sub

Thank you!!!

Maybe:

Sub POMarreco(): Dim i As Long, r As Long, c As Long, PA As String, Q As Range
        r = Rows.Find("*", , , , xlByRows, xlPrevious).row
    c = Columns.Find("*", , , , xlByColumns, xlPrevious).Column
For i = 1 To r Step 44
Set Q = Cells(i + 43, c)
PA = "$A$" & i & ":" & Q.Address
ActiveSheet.PageSetup.PrintArea = PA
ActiveSheet.PrintOut Copies:=1
Next i
End Sub

Thanks to respond.

i have bad english, but i try explain.

look my newfile, inside file has 45 row (colorful background), i need skip 1 blank row (sum total 45 per page).

in file has 20.000 rows, i need print , 5 gruop date each 8 row (1 group + blanck cell, 2 group + blanck cell, 3 group + blanck cell, 4 group + blanck cell, 5 group + blanck cell) = 45 rows

colors will try to clarify my explanation.

Thank you!

Hi Marreco (Old Friend),

This looks good:

Sub POMarreco(): Dim i As Long, r As Long, c As Long, PA As String, Q As Range
        r = Rows.Find("*", , , , xlByRows, xlPrevious).Row
    c = Columns.Find("*", , , , xlByColumns, xlPrevious).Column
For i = 1 To r Step 45:  Set Q = Cells(i + 44, c)
        PA = "$A$" & i & ":" & Q.Address
With ActiveSheet.PageSetup: .PrintArea = PA: .Zoom = False
.FitToPagesWide = 1: .FitToPagesTall = 1: End With
ActiveSheet.PrintOut Copies:=1
Next i: End Sub

Hi Great Friend.

before running this macro on my computer (because there are many impressions), I want to print preview.

I fear that I must preview before printing.

how to change your code so that I can print preview?

Thank you!

Hi Marreco,

The way I ran it for Print Preview was to just comment out the Printout Line. But the data was uneven so I added the SpacingData procedure - be sure to have a backup before you run it!

Sub POMarreco(): Dim i As Long, r As Long, c As Integer, t As Integer, PA As String
        r = Rows.Find("*", , , , xlByRows, xlPrevious).row
SpacingData: For i = 1 To r
If Range("A" & i) = "Giro" Then
If i > r - 7 Then Exit For
Do Until Range("A" & i + 2) = "Cliente"
Range("A" & i + 1).EntireRow.Delete Shift:=xlUp
r = r - 1: Loop: End If: Next i
            t = Int(r / 45) + 1: PA = "$A1:$B$" & r
With ActiveSheet.PageSetup: .PrintArea = PA: .Zoom = False
.FitToPagesTall = t: End With
'ActiveSheet.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintPreview
End Sub

After you get the print preview, you should just be able to hit the print button in your toolbar.

Let me know of issues And, thanks for the rep!

Hi, great xladept, the code was wonderful!

Your skills are amazing!

thank you very much!

You're welcome old friend! And, thanks for the compliment