Today on Episode 360, a viewer question stumps MrExcel. Well, there are two solutions in the podcast, but like you, I have to believe there is a better way. The question is how to add a page break every 35 rows. If you know a better way to do this, leave a message at 866-581-0221.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have a viewer call in, and I'm not sure that I know the best way to get the solution.
So I'm actually going to open this up to everyone to see if anyone has a way to solve this problem.
First, here's the call: “Hi Mr. Jelen, I was wondering if there's a way to set up Page Break intervals of 35 rows?” OK, page breaks every 35 rows, now the first way I'm going to do this, it's really kind of cheating, right now, because we have 14 point type, we're getting a page break after every 39 rows.
So one way to approach this is File, Page Setup, and then just change the bottom margin up or something like that, until you get to the point where it's breaking at every 35th row.
I think that's kind of cheating, and not really in the spirit of what he was trying to do.
My first attack at this was, I basically put in a temporary column and filled in the numbers 1-35, and then used the NA function.
=NA() and then a formula that would basically copy from 35 rows down, and what that gets me is every 35th row I have #N/A. And I thought what I could do is select the entire row, Edit, Go To Special, and then choose that I want to see all the formulas that evaluate as Errors, which would select all of the error cells for me.
My problem there, though, is that using Insert, Page Break, only does the cell in the upper left-hand corner, so that does not solve the problem.
My next solution is so convoluted, I hate to even suggest it, but let's just go through it.
I'm going to insert another temporary column and I'll just call it Temp.
What I'm basically going to do is put a number 1 in row 2, and the formula after that says, basically, we're going to copy the formula all the way down.
And every 35th row, so for example at row 37, the new formula is going to say =1+ the cell above it.
And then the next formula basically says we're going to take the cell above us, plus the cell 35 rows above us, minus the cell 36 rows above us, that way, we can get a sense for how much has changed.
We’ll copy that formula down, it will basically increment the number every 35, rows so we'll eventually get to the 3’s, the 4’s, the 5’s.
Now, once I have this, then I'm going to come out to the right-hand side of the data, add another temporary column with the number 1, copy that all the way down.
Finally, use the Data, Subtotals trick, At each change in the first Temp, we're going to add a subtotal to second Temp and oh, by the way, Page break between groups.
It'll go through and add all the page breaks, and then we can basically delete the temporary column on the right, delete the temporary column on the left, leaving us our effect, but this is really convoluted!
If you know a better way to solve this problem, please, leave a voicemail, 866-581-0221, and we'll use your idea in a netcast next week.
Hey, thanks for stopping by, there's a couple of really bad ways to put a Page Break every 35th row, let's see if anyone out there knows a better way to solve that problem!
Today we have a viewer call in, and I'm not sure that I know the best way to get the solution.
So I'm actually going to open this up to everyone to see if anyone has a way to solve this problem.
First, here's the call: “Hi Mr. Jelen, I was wondering if there's a way to set up Page Break intervals of 35 rows?” OK, page breaks every 35 rows, now the first way I'm going to do this, it's really kind of cheating, right now, because we have 14 point type, we're getting a page break after every 39 rows.
So one way to approach this is File, Page Setup, and then just change the bottom margin up or something like that, until you get to the point where it's breaking at every 35th row.
I think that's kind of cheating, and not really in the spirit of what he was trying to do.
My first attack at this was, I basically put in a temporary column and filled in the numbers 1-35, and then used the NA function.
=NA() and then a formula that would basically copy from 35 rows down, and what that gets me is every 35th row I have #N/A. And I thought what I could do is select the entire row, Edit, Go To Special, and then choose that I want to see all the formulas that evaluate as Errors, which would select all of the error cells for me.
My problem there, though, is that using Insert, Page Break, only does the cell in the upper left-hand corner, so that does not solve the problem.
My next solution is so convoluted, I hate to even suggest it, but let's just go through it.
I'm going to insert another temporary column and I'll just call it Temp.
What I'm basically going to do is put a number 1 in row 2, and the formula after that says, basically, we're going to copy the formula all the way down.
And every 35th row, so for example at row 37, the new formula is going to say =1+ the cell above it.
And then the next formula basically says we're going to take the cell above us, plus the cell 35 rows above us, minus the cell 36 rows above us, that way, we can get a sense for how much has changed.
We’ll copy that formula down, it will basically increment the number every 35, rows so we'll eventually get to the 3’s, the 4’s, the 5’s.
Now, once I have this, then I'm going to come out to the right-hand side of the data, add another temporary column with the number 1, copy that all the way down.
Finally, use the Data, Subtotals trick, At each change in the first Temp, we're going to add a subtotal to second Temp and oh, by the way, Page break between groups.
It'll go through and add all the page breaks, and then we can basically delete the temporary column on the right, delete the temporary column on the left, leaving us our effect, but this is really convoluted!
If you know a better way to solve this problem, please, leave a voicemail, 866-581-0221, and we'll use your idea in a netcast next week.
Hey, thanks for stopping by, there's a couple of really bad ways to put a Page Break every 35th row, let's see if anyone out there knows a better way to solve that problem!
