Excel, one of the world’s biggest-selling spreadsheets has many features you can take advantage of but sometimes the standard features just don’t do the job. One example is managing page breaks; in particular, we’ll look at how you can create and delete multiple page breaks – all in a few lines of VBA code.
Inserting Page Breaks
If you’re preparing a report for your boss, then chances are it needs to be printed out and there’s nothing worse than new pages not being printed at the right place. But instead of manually inserting the page breaks where you want them, why not try some VBA code?
First, let’s create record a macro of a page break to see what the code might look like. We’ll have to tweak it a little bit at least it will give us a general idea.
The code as it stands adds a break above the active cell – where you last positioned the cursor. That’s good, but not quite what we’re looking for; we want to be able to add multiple breaks at locations of our choice.
One way of doing this is to manually select multiple cells, using the ctrl key, and then run a VBA routine to add page breaks at each of the locations. The VBA code, which you could assign to a custom menu button would go something like this:
For Each breaklocation In Selection Before:= breakLocation Next
The code simply loops through a “collection” of locations that you have selected with your cursor and adds a new page command one cell above each selection.
Removing Page Breaks
You might even want to reset the breaks before running the code, but that’s up to you. Anyway, to remove all page breaks on the current sheet here’s the code:
You can see it’s quite simple to fine-tune the page break commands that come with Excel with just a little knowledge and commitment to learning something new. And this is where the ability to record macros in Excel is so powerful; to find out how to do something you can usually just record a macro and then pull apart the code to meet your own needs.
You could fine-tune the code to meet your own needs; perhaps you want to set up your page breaks every 20 rows, or whenever the day of the week is a Sunday? You could even enter data and use a special word or character as a marker for where you want a break.
While slightly more challenging, the techniques remain the same for creating macros and VBA code to address your own Excel problems. It’s all a matter of working out what you want to achieve and remembering that what Excel can do, you can do!
John Abrams is a Microsoft Office expert and he has been working in the technology industry for the last 5 years. As a technical expert, he has written technical blogs, white papers, and reviews for many websites such as office.com/setup