Tina C asks if there is a better way to add a page break every 35 rows. Way back in episode 360, I showed a few methods. But today, one line of VBA in the Immediate Window seems much easier. You have to edit this line in three places:
For i = 37 To 1446 Step 35 : ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i, 1) : Next
For i = 37 To 1446 Step 35 : ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i, 1) : Next
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2457. Excel Page break every 35 rows.
Hey welcome back to MrExcel netcast.
I'm Bill Jelen.
This is great, video number 360 from 12 years ago.
Someone wanted a page break every 35 rows and I didn't have a really good method.
I actually asked people, “hey down in the YouTube comments let me know”.
Just yesterday Tina Collins asked, has anyone figured this out yet?
Oh my God, I went back and watched the video.
What a convoluted set of steps I had back then. I have no idea why back then I didn't just go straight to VBA because VBA is certainly the way to do this.
So three questions you have to answer with your data set.
The first row on the second page is going to be row number 37.
I want to press Ctrl+Down Arrow.
The last row with data is 1446.
and just for the heck of it I'm going to do every 35.
So, 37 is the 1st place where we put a page break.
1446 is the last row with data.
And we want a page break every 35 rows.
So you're going to copy this.
I'll put this down in the YouTube description.
Copy this to notepad or something like that.
Edit the 37, edit The 1446, edit the 35.
And then we're going to copy that to the clipboard with Ctrl+C.
Now on a Windows PC, you are going to do Alt+F11.
On a Macintosh, you want to display the Developer tab and click Visual Basic.
If you don't have the Developer tab, Right-click, Customize the Ribbon and over here on the right hand side just check Developer.
So here's Visual Basic.
Good chance is going to open completely blank like this.
Control G control G for the immediate window and then paste.
So for I = 37 to 1446, step 35.
You see the flashing insertion point right here is right after the word Next.
Just press Enter and we should be good to go.
File, Close and Return to Microsoft Excel (which is Alt+Q).
And then do Ctrl+P for Print Preview.
And you'll see there is our first page and here is the second page, and our third page and so on.
So it's successfully added the page breaks in.
It's funny, you can't see the page breaks till you do a print preview first, Make sure to do the Print Preview or you'll think that nothing happened.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank Tina for finding that old video and seeing if there's a better way.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Hey welcome back to MrExcel netcast.
I'm Bill Jelen.
This is great, video number 360 from 12 years ago.
Someone wanted a page break every 35 rows and I didn't have a really good method.
I actually asked people, “hey down in the YouTube comments let me know”.
Just yesterday Tina Collins asked, has anyone figured this out yet?
Oh my God, I went back and watched the video.
What a convoluted set of steps I had back then. I have no idea why back then I didn't just go straight to VBA because VBA is certainly the way to do this.
So three questions you have to answer with your data set.
The first row on the second page is going to be row number 37.
I want to press Ctrl+Down Arrow.
The last row with data is 1446.
and just for the heck of it I'm going to do every 35.
So, 37 is the 1st place where we put a page break.
1446 is the last row with data.
And we want a page break every 35 rows.
So you're going to copy this.
I'll put this down in the YouTube description.
Copy this to notepad or something like that.
Edit the 37, edit The 1446, edit the 35.
And then we're going to copy that to the clipboard with Ctrl+C.
Now on a Windows PC, you are going to do Alt+F11.
On a Macintosh, you want to display the Developer tab and click Visual Basic.
If you don't have the Developer tab, Right-click, Customize the Ribbon and over here on the right hand side just check Developer.
So here's Visual Basic.
Good chance is going to open completely blank like this.
Control G control G for the immediate window and then paste.
So for I = 37 to 1446, step 35.
You see the flashing insertion point right here is right after the word Next.
Just press Enter and we should be good to go.
File, Close and Return to Microsoft Excel (which is Alt+Q).
And then do Ctrl+P for Print Preview.
And you'll see there is our first page and here is the second page, and our third page and so on.
So it's successfully added the page breaks in.
It's funny, you can't see the page breaks till you do a print preview first, Make sure to do the Print Preview or you'll think that nothing happened.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank Tina for finding that old video and seeing if there's a better way.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Last edited by a moderator:
