[Excel VBA] How to Automatically Set Page Breaks at Fixed Intervals

Introduction

When printing Excel sheets with large amounts of data, page breaks often occur in unintended places, cutting off tables mid-row and making them difficult to read. Manually setting page breaks one by one is extremely time-consuming for large datasets.

Using VBA, you can automatically insert page breaks based on rules like “every X rows” or “every Y columns.” In this article, I will explain a reusable VBA code snippet to tidy up your print layouts.

Completed VBA Code

Below is the VBA code that automatically sets manual page breaks on the active sheet at specified row and column intervals.

Sub SetCustomPageBreaks()

    '--- Settings ---
    ' Specify the interval for page breaks using constants
    Const ROW_INTERVAL As Long = 20 ' Page break every 20 rows
    Const COLUMN_INTERVAL As Long = 5  ' Page break every 5 columns
    '--- End Settings ---

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim lastCell As Range
    Dim i As Long

    ' Set the currently active sheet as the target
    Set targetSheet = ActiveSheet
    
    ' Preparation and Setup
    With targetSheet
        ' Set the view mode to "Normal" (Page breaks may fail in other views)
        .Parent.Windows(1).View = xlNormalView
        
        ' 1. Reset all existing page breaks
        .Cells.PageBreak = xlNone
        
        ' 2. Get the last cell with data
        '   Avoid error if UsedRange is empty
        If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
        Set lastCell = .UsedRange.Cells(.UsedRange.Cells.Count)
        
        ' 3. Insert "Row" page breaks at specified intervals
        For i = ROW_INTERVAL + 1 To lastCell.Row Step ROW_INTERVAL
            .Rows(i).PageBreak = xlPageBreakManual
        Next i
        
        ' 4. Insert "Column" page breaks at specified intervals
        For i = COLUMN_INTERVAL + 1 To lastCell.Column Step COLUMN_INTERVAL
            .Columns(i).PageBreak = xlPageBreakManual
        Next i
    End With
    
    ' Completion Message
    MsgBox "Page breaks have been set. Please check 'Page Break Preview' in the 'View' tab."

End Sub

Explanation of Key Points

1. Specifying Intervals with Constants

Const ROW_INTERVAL As Long = 20
Const COLUMN_INTERVAL As Long = 5

At the beginning of the code, the intervals for inserting page breaks are defined for rows and columns respectively. You can easily customize the layout just by changing these numbers.

2. Resetting All Existing Page Breaks

.Cells.PageBreak = xlNone

Before running the macro, manual page breaks set previously might still exist. By clearing all page breaks in the entire sheet using xlNone first, we ensure that the result is consistent every time the macro is run. This is a very important initialization step.

3. Getting the Last Cell of Data

Set lastCell = .UsedRange.Cells(.UsedRange.Cells.Count)

We use the .UsedRange property to let VBA automatically determine where the data ends. This retrieves the bottom-right cell (lastCell) of the data range, which provides the row and column numbers used as the upper limit for the loop.

4. Inserting Page Breaks using a Loop

For i = ROW_INTERVAL + 1 To lastCell.Row Step ROW_INTERVAL
    .Rows(i).PageBreak = xlPageBreakManual
Next i

Using a For...Step loop, we increment the counter variable i by the specified interval (ROW_INTERVAL) and insert page breaks.

• .Rows(i).PageBreak = xlPageBreakManual: Inserts a manual page break above the specified row (i).
• .Columns(i).PageBreak = xlPageBreakManual: Inserts a manual page break to the left of the specified column (i).

Verifying the Results

After running this macro, click on “Page Break Preview” in the Excel View tab.

You will see blue dotted lines (manual page breaks) drawn at the set intervals on the sheet. This allows you to visually confirm that the page boundaries for printing are set as intended.

Summary

Setting page breaks for printing large amounts of data neatly can be completed instantly using VBA.

1. Clear existing page breaks with xlNone.
2. Identify the end of the data with UsedRange.
3. Use a For...Step loop to set xlPageBreakManual at fixed intervals.

This procedure significantly reduces the effort required for routine printing tasks and enables standardized operations where anyone can obtain the same print result.