Free Microsoft Excel 2013 Quick Reference

Dynamic Page breaks with repeated row

I found this macro on the net, and I adapted it to grab a dynamic number for the row divisions ("Pages" public variable) problem is row 1 is repeated at the top of each page, so the first sheet has one less row then the rest of the sheets so i end up with the last sheet having one row on it. anybody know where the problem is? any help would be much appreciated



Sub PrintAreaWithpageBreaks()
Dim pages1 As Integer
Dim pageBegin As String
Dim PrArea As String
Dim i As Integer
Dim q As Integer
Dim nRows1 As Integer, nPagebreaks As Integer
Dim R1 As Range
Set R1 = ActiveSheet.UsedRange
'add pagebreak every X rows

nRows1 = R1.Rows.Count
If nRows1 > pages Then
  nPagebreaks = Int(nRows1 / pages)
  For i = 1 To nPagebreaks
     ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R1.Cells(pages * i + 1, 1)
  Next i
End If

'can be used in a separate macro, as I Start counting the number of pagebreaks
pages1 = ActiveSheet.HPageBreaks.Count
pageBegin = "$A$1"
For i = 1 To pages1
  If i > 1 Then pageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address
  q = ActiveSheet.HPageBreaks(i).Location.Row - 1
  PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))
  ActiveSheet.PageSetup.PrintArea = PrArea
  ' the cell in column 1 and in the row immediately below the pagebreak
Next i

End Sub

Post your answer or comment

comments powered by Disqus
I have an excel sheet with 30,000 rows of data which i need to insert a page break every 100th row- any ideas for a vba solution?


I searched and was not successful solving this simple issue.

I want to set the horizontal page breaks at specific rows. I have rows hidden in the sheet, and would like to adjust the print area and the page breaks as the number of rows change.

For example:

break 1 = above row 50
break 2 = above row 160

Thank you for helping me.

Excel 2000

I have this code but it returns an error.
Sub Insert_PageBreaks()
With ActiveSheet
        'Remove all PageBreaks
        .HPageBreaks.Add Before:=.Cells(50, 1)
End With

End Sub

Wanting to add a page break to the row above each time a certain text string occurs in col 1. The text string is "Contract Inquiry".

Is there a way to ignore page breaks in hidden rows when printing? When I
hide rows that contain page breaks a blank page still prints (except for the
print titles).

I'd rather not use any vba since the security settings of the many users
often block the macros.

Any suggestions?


I have a work sheet with alot of columns with each having a different header
subject. Each column has hundreds of rows of data. How do I set a page break
where the headers for the columns will appear on each page. Also if I delete
a row it will automaticly bump the next row up without effecting the column
headers. Please help. Thank you


First time posting, please be patient.

I'm working with a large Excel file with rows of irregular heights that will be printed into a book. The tab I'm working on would normally print to 70 pages.

The rows are divided into different sections, and each section has a title/header above it. Some sections are very long and others are short. I'd like one of these title/headers to appear at the top of each printed page. I can't use repeat rows at top because the text of the title will vary depending on what is on the row beneath it.

I tried writing some code to allow the user to choose a cell, and then the macro would insert a break and a row with the correct format, but this is unbearably slow for a 70 pg doc that has to be recreated 3x/month. So I'm trying to do it for the whole doc at once.

I tried resetting all page breaks, the problem is that when I insert a row near one of the page breaks, sometimes the row will insert below the page break and sometimes it will insert above. Inserting a manual page break sometimes replaces the automatic page break and sometimes does not. Then when I resize the row, the page break may reposition itself.

Any ideas about where to go from here? The only other thing I can think of is to use a counter, add row heights to it, and then when it gets above a certain number to insert the row and to format it and mark the row in some way, then go back through the doc, find all the automatic page breaks, and drag them in the direction of the marked row. This seems really complicated.


Hey excel forum I'm working on a new project that I need a bit of help getting started. I'm developing a macro that will dynamically format the first and last row of data between page breaks. I can't seem to figure out how to detect page breaks correctly. I have two functions I've been messing around with that may or may not help the situation. One function returns true or false depending on an HPageBreak.

Function pagebreak() As Boolean
Dim i As Integer
pagebreak = False
Dim myRange As Range
Set myRange = Application.Caller
With myRange
    For i = 1 To .Worksheet.HPageBreaks.Count
        If .Worksheet.HPageBreaks(i).Location.Row <= .Row Then
            If .Worksheet.HPageBreaks(i).Location.Row = .Row Then
                pagebreak = True
                Exit Function
            End If
            Exit Function
        End If
End With
End Function
and the other function right now just gets the last row of data in a worksheet but I was hoping with the function pagebreak to loop through and grab the row number of the first and last row in a page break with this function.

Function GetRow(strSheet, strColum) As Long
Dim wb As Workbook, ws As Worksheet
Dim myRange As Range

Application.ScreenUpdating = False

    Set myRange = Worksheets(strSheet).Range(strColum & "1")
    GetRow = Cells(65536, myRange.Column).End(xlUp).Row

Application.ScreenUpdating = True

End Function


My aim is to avoid manually adjusting page breaks.

I have a spreadsheet with multiple rows of variable length. Every few rows
there is a sub-heading for the following few rows. Because the content of
the rows can vary in length, there is no way I can "hard-code" page breaks.

Is there any way to "link" the sub-heading row to the content rows so they
print on the same page?

I have an intermittent problem with excel not resetting automatic page breaks below a hard page break. I think it has something to do with some macros that I run but I have no idea which ones they could be. I thought I had it narrowed down a while back so I stopped using some but the problem is coming back. I’ve tried searching here in the past but was unable to find a solution.

To more clearly explain the problem.

Say the first automatic page break is at row 100. I insert a hard horizontal page break at Row 95. The automatic page break doesn’t go away. There’s a hard break at row 95 and a soft break at row 100. It doesn’t affect printing but it’s a problem for me because I have a macro that finds the automatic page breaks and it still picks up this bogus page break.


I have approx 500 pages to print, As you all know excel cannot repeat
rows at the bottom of each page.
I need to move to every page break insert 5 rows before a page break ,
go to the next sheet copy the 5 rows from the next sheet and paste this
information into the inserted rows then go to the next page break and do
the same until the last horizontal page break is complete.


*** Sent via Devdex ***
Don't just participate in USENET...get rewarded for it!

I have 2 Excel spreadsheets. I can print one no problem. On the problem
sheet when I do print preview, it shows the first page as printing just
fine, but after that I get an automatic page break inserted after EVERY row.
The printer then starts pumping out paper.

"Reset page breaks" doesn't work. I've tried all possible combinations in
Page Setup from "1 page wide and length left blank" to "Adjust to 79%". I
still get the same results. One other clue. When I do Print Preview and
look at the first page that is going to print just fine, I can drag the
scroll bar down and the image flutters, but the image never moves down the

If I can print one spreadsheet OK and the other one has the problems
mentioned above, maybe I'm doing something wrong that is inherent in the
spreadsheet and not with the Print/Page Setup functions.

As usual,
Thanx much,

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.


I need Excel to insert a page break at every blank row. The number of rows
per page will be different each time I run the report, so the row number will
change each time. I have been inserting the page breaks manually, but it is
a very time consuming process with such a long report.

Hi All,

Please help! I'm having trouble coming up with the logic for this:

I want to set page breaks to a dynamic table. The problem is the column widths will always change, AND every 4 columns must always be displayed together.

So for instance, I need to add up the column widths for every 4 columns, and then display on a page the maximum set of 4 columns that will fit before moving on to the next page. If 9 columns will fit on a page, I want to display 8. If 16 will fit, I want to display 16. Hope this makes sense...

In Excel (2007), I am looking for a way to set a page break into empty-spaced cells between customer information cards. Each 'card' is separated by 3 or 4 empty rows and instead of manually going down 100-150 pages and setting a page break between cards, it would be great to have a way to get the page breaks between the cards with a command. The 'cards' are not the same length, but they are all the same width. If anyone has any suggestions for me to automatically insert page breaks where there are empty rows, I'd sure appreciate it.
There is an example attached that has two of the 'cards'. There are over 100 cards to a spreadsheet and I'd like to separate each one.

I am building a spreadsheet with groups of 2-8 rows merged in column A. Is
there a way to insert a conditional page break if a specific group of merged
cells won't fit together at the bottom of a page. I know page breaks can be
inserted manually, but I don't want to have to go back and adjust them all as
I add more data within the spreadsheet. Would some sort of macro work?

I have a worksheet that is setup fit to page. Right now it has about 1000 rows of data with about 200 sections that can have anywhere from 3 to 6 rows allotted per section.

The problem is when I print, invariably some sections end up page breaking in the middle of a section.

Is there any way in excel to insure that certain rows of data will never page break. I cannot insert manual breaks as the size of the sections and the size of the data will continually change.

I know in word there is something called keep lines together that prevents paragraphs from page breaking. I need something similar in excel.

Any help is greatly appreciated.

I've been asked to find a way to print comments at the bottom of each page of a printout. The problem is that the size of comment exceeds the space allowed in the footer section. The page is set up in portrait. I'd like to be able to count the number of rows, then insert 10 rows, then copy the comments into those rows, then print. For example, let's say that I after row 79 I need to insert 10 rows, add the comments, force a page break. Then repeat for the rest of the report - after the next 79 nows, do the same thing.


Sharon Daniel


I'm trying to write a macro that looks through Column A, finds any cell with the word "Page Break", deletes that row, and adds a Horizontal Page Break in its stead. Also, since there are already incorrect Horizontal PageBreaks, is there a way to take those out while keeping the good ones (from the macro) in? Can anyone help me?

Thanks a bunch in advance! =D


I am reformatting existing excel files and need to remove any existing page breaks and replace them with specific breaks. Also, is there a way to insert horizontal page breaks at 32 row increments for different size files? Current code is

Set ActiveSheet.VPageBreaks(1).Location = Range("O1") 
 'vertical oage break at column AG1
Set ActiveSheet.VPageBreaks(2).Location = Range("AG1") 
 'sets horizontal page break at row32
Set ActiveSheet.HPageBreaks(1).Location = Range("A32") 
 'sets horizontal page break at row 64
Set ActiveSheet.HPageBreaks(2).Location = Range("A64") 
 'sets horizontal page break at row 96
Set ActiveSheet.HPageBreaks(2).Location = Range("A96") 
 'sets horizontal page break to row 128
Set ActiveSheet.HPageBreaks(2).Location = Range("A128") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get an error if the file is smaller than 128 rows.

I have a macro that uses subtotals on a dynamic list to generate page breaks by Department. Rows 1:6 of the table are set to print at the top of each page and column A is not set to print at all. Would it be possible to get cell B6 to return the value of the first cell of each page break (from column A) when the page prints. (Column A contains the department names, and though I don't want to print the column itself, I would like the name of each department to appear at the top of the page.)

My first post, and my first attempt at Macros.

I need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it...could someone help me with this? Any help is appreciated. Thanks!

i've written a code as per the following:

Sub PrintRange1()
ActiveSheet.PageSetup.PrintArea = Range("range1").Address
ActiveSheet.Rows(53).PageBreak = xlPageBreakManual
ActiveSheet.Rows(110).PageBreak = xlPageBreakManual
ActiveSheet.Rows(152).PageBreak = xlPageBreakManual
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 4
ActiveSheet.PageSetup.Orientation = xlPortrait
End Sub
Sub PrintRange2()
ActiveSheet.PageSetup.PrintArea = Range("range2").Address
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub
Sub PrintRange3()
ActiveSheet.PageSetup.PrintArea = Range("range3").Address
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub

the code at the moment allows me to:

Range1 = prints 4 pages of document in portrait
Range2 = prints 1 page in landscape
Range3 = prints 1 page in landscape

What I’m trying to do is make page breaks in range1 so that when the four pages print there is a break at row 53, row 110 and row 152. The code is in there but not working and I can’t figure out why. The page setup codes are working fine.

any help would be appreciated.


Actually it really comes down to the simple fact that I want rows 1 and 2 to reprint on all pages as well as rows 3-10 in columns A and B to reprint. If rows 3-10 in columns A and B need to be on every worksheet that is fine.

Original question:
I have a named "legend" on Sheet1 that is in cells A1:B10, which holds the "Legend" of the colors I am using in other worksheets. I would like to make sure that each worksheet (which spans multiple pages) has the same Legend in the side 2 columns of each page along with repeating rows 2 and 3.

Sheet 1
column a column b
red in jeopardy
yellow at risk
green on target

Sheet 2

column a column b column c column d
row 1 project status
row 2 alpha alpha
row 3 beta
row 4 gamma

would like to print out without having to worry about “Legend” and page breaks ….
Page 1
column a column b column c column d
row 1 red in jeopardy project status
row 2 yellow at risk alpha (red)
row 3 green on target beta (yellow)
row 4 gamma (green)

Page 2
repeat column a and b rows 1 - 3
and row 1

No luck finding an answer? You could always try Google.