How to use VBA to set up equal number of Rows per Page for a worksheet for printing

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
112
Hi,

I have a worksheet to print but sometimes excel print the last page with only one line, is it something can be defined to print equal number of the rows per page in order to combine the last page with one line into the 2nd last page but it will be good to have equal number of the rows per page when printing out.

Cheers,

Peter
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Did you try the "fit on page" option? It should reduce the scale a bit so it fits all on one page.
 
Upvote 0
Hi, I tried it but excel will still leave the last page with 1 line if there are not enough contents.

It will be nice to have a code to ensure all pages showing equal rows for better presentation purposes.

Cheers,

Peter
 
Upvote 0
Hi,

Sorry about this and just try to have more advice if possible.

Happy to delete any if possible.

Cheers,

Peter
 
Upvote 0
Try this sub, let me know if you like it
Code:
Option Explicit

Sub paginate()
'insert page breaks in activesheet
'to avoid last page with just a few lines

  Const minNbrLinesOnPage As Long = 5   '<= change to your taste
  Const maxNbrLinesOnPage As Long = 40  '<=
  
  Dim nbrRowsToReport     As Long
  Dim nbrFullPages        As Long
  Dim nbrPages            As Single
  Dim nbrLinesOnLastPage  As Long
  Dim newNbrLinesPerPage  As Long
  Dim r As Long
  
  nbrRowsToReport = Range("A1").CurrentRegion.Rows.Count
  nbrPages = nbrRowsToReport / maxNbrLinesOnPage
  nbrFullPages = CLng(nbrPages)
  nbrLinesOnLastPage = nbrRowsToReport - nbrFullPages * maxNbrLinesOnPage
  
  If nbrLinesOnLastPage < minNbrLinesOnPage And _
     nbrLinesOnLastPage > 0 Then
    
    newNbrLinesPerPage = nbrRowsToReport / (nbrFullPages + 1)
    ActiveSheet.ResetAllPageBreaks
    r = newNbrLinesPerPage
    While r < nbrRowsToReport
      Rows(r).PageBreak = xlPageBreakManual
      r = r + newNbrLinesPerPage
    Wend
  End If
End Sub
 
Upvote 0
Try this sub, let me know if you like it
Code:
Option Explicit

Sub paginate()
'insert page breaks in activesheet
'to avoid last page with just a few lines

  Const minNbrLinesOnPage As Long = 5   '<= change to your taste
  Const maxNbrLinesOnPage As Long = 40  '<=
  
  Dim nbrRowsToReport     As Long
  Dim nbrFullPages        As Long
  Dim nbrPages            As Single
  Dim nbrLinesOnLastPage  As Long
  Dim newNbrLinesPerPage  As Long
  Dim r As Long
  
  nbrRowsToReport = Range("A1").CurrentRegion.Rows.Count
  nbrPages = nbrRowsToReport / maxNbrLinesOnPage
  nbrFullPages = CLng(nbrPages)
  nbrLinesOnLastPage = nbrRowsToReport - nbrFullPages * maxNbrLinesOnPage
  
  If nbrLinesOnLastPage < minNbrLinesOnPage And _
     nbrLinesOnLastPage > 0 Then
    
    newNbrLinesPerPage = nbrRowsToReport / (nbrFullPages + 1)
    ActiveSheet.ResetAllPageBreaks
    r = newNbrLinesPerPage
    While r < nbrRowsToReport
      Rows(r).PageBreak = xlPageBreakManual
      r = r + newNbrLinesPerPage
    Wend
  End If
End Sub

Hi ask2tsp

Thanks for your code and I will have a look thanks
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top