Month: April 2021

In this article, you will learn how to insert page break manually, page break VBA to auto insert multiple page breaks and how to delete page breaks in Excel.

Page break is an option which enables users to specify after which row or column a new print page should start. 

Using page break you can split one page into multiple pages for print.

Insert page break in Excel (Hindi Speech)

Insert page break in Excel manually

Here you will see how to insert page break in Excel manually.

You can check Excel default page break view by going to the View tab then click on Page Break Preview.

Page Break Preview option is also present at the bottom in the status bar.

You can see only one page and one page break in the page break view.

Insert vertical page break

Here I want to limit number of columns to be printed on a page. 

Currently all columns in coming in the print page but I want to split this into two parts. 1st is upto sales column and 2nd sales columns onward.

For this vertical page break is needed. 

To apply vertical page break select E column range then go to Page Layout tab and from Breaks drop-down click on the Insert Page Break.

In the below image you can see page got splitted into two parts vertically.

If you print the above pages then in the 1st page Segment, Country, Product, Sales column data will get printed and 

in 2nd page Profit, Date, Month, Year column will get printed.

To bring first 3 columns Segment, Country, Product in all the pages,

 go to Page Layout tab then click on Print Titles, in Columns to Repeat at Left select first three columns $A:$C the click on the Ok button.

Insert horizontal page break

To insert a horizontal page break, select a row range from where you want to insert page break.

Go to the Page Layout tab and in Break drop-down option click on Insert Page Break. 

In the below GIF you can see after each change in segment value page break is created. 

Watch video Insert Page Break when values changes

Using Subtotal to insert page breaks

• To insert page break using Subtotal first select range.
• Go to Data tab click on Subtotal option.
• In Subtotal pane At Each Change in the select column to insert page break for each value change.
• Select function and column on which function to perform.
• Tick on Replace current subtotal and Page break between groups.
• Click on OK.

Below is the page break view after applying subtotal.

If you do not want subtotal rows to print then you can hide them then take print out.

Auto insert page break in Excel using VBA

• Copy past the below VBA code for page break in the standard module of VBA IDE.
• Make sure the column is sorted based on which you want to insert page break when value changes in that column.
• Select range or table.
• Run code, in the input box write name of the column (in this case A) based on which you want to insert page break. 

Note:- This VBA code is write once and use on all the open workbooks type. So you just need to save this VBA code in a workbook (.xlsm or .xlsb format type) then you can use it in any open workbook. 

Sub insertHorizPageBrk()
'page break when value changes in column
  Dim rng As Range, clmn As String
  Dim frstRw As Long, grpCnt As Long
  Dim clmnRng As Range, ttlRws As Long
  Dim cel As Range, lrw As Long, frstClmn As Byte
  Set rng = Selection
  clmn = InputBox("Insert column Name letter")
  frstRw = rng.Cells(1, 1).Row
  frstClmn = rng.Cells(1, 1).Column
  With ActiveSheet.PageSetup
     ActiveSheet.ResetAllPageBreaks
    .PrintArea = rng.Address
    .PrintTitleRows = Rows(frstRw).Address
  End With
  ttlRws = rng.Rows.Count
  Set clmnRng = Range(clmn & frstRw).Resize(ttlRws)
  Set cel = clmnRng.Cells(2, 1)
  grpCnt = Application.WorksheetFunction.CountIf(clmnRng, cel.Value)
  Set cel = cel.Offset(grpCnt)
  lrw = rng.Cells(1, 1).Offset(ttlRws).Row
  Do While cel.Row < rng.Cells(1, 1).Offset(ttlRws).Row
     Cells(cel.Row, frstClmn).PageBreak = xlPageBreakManual
     grpCnt = Application.WorksheetFunction. _
     CountIf(cel.Resize(ttlRws + frstRw - cel.Row + 1, 1), cel.Value)
     Set cel = cel.Offset(grpCnt)
  Loop
End Sub

Delete page break in Excel

Delete specific page break

To remove a specific page break select the row range above which page break is there.

In Page Layout tab, in the Breaks option select Remove Page Break.

Delete all page breaks (reset all page breaks)

To delete or reset all page breaks of a sheet, go to Page Layout tab, in Breaks option click on Reset All Page Breaks.