How To Manage Excel Page Breaks With VBA Macro Code

How To Manage Excel Page Breaks With VBA Macro Code
By Andy L Gibson

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, the 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 but at least it will give us a general idea.


ActiveSheet.HPageBreaks.Add Before:=ActiveCell
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 ActiveSheet.HPageBreaks.Add 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:


ActiveSheet.ResetAllPageBreaks
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!
Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business. Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities wherever they might present themselves.
Article Source: http://EzineArticles.com/?expert=Andy_L_Gibson
http://EzineArticles.com/?How-To-Manage-Excel-Page-Breaks-With-VBA-Macro-Code&id=7210112