XL97: Run-Time Error 9 When Using a Macro to Remove Page Break

This article was previously published under Q170635
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel 97, if you run a Visual Basic for Applications macrothat attempts to remove a vertical or horizontal page break from aworksheet, you may receive the following error message:
Run-time error '9':
Subscript out of range
This problem occurs if the following conditions are true:

  • If the page break is a vertical page break, the problem occurs if no data is located to the right of the page break. -or-

  • If the page break is a horizontal page break, the problem occurs if no data is located below the page break. -and-

  • The macro uses either VPageBreaks(x).Delete or HPageBreaks(x).Delete to delete a page break. For example, either of these lines of code may fail:
Microsoft provides examples of Visual Basic for Applications procedures forillustration only, without warranty either expressed or implied, including,but not limited to the implied warranties of merchantability and/or fitnessfor a particular purpose. The Visual Basic procedures in this article areprovided 'as is' and Microsoft does not guarantee that they can be used inall situations. While Microsoft support professionals can help explain thefunctionality of a particular macro, they will not modify these examples toprovide added functionality, nor will they help you construct macros tomeet your specific needs. If you have limited programming experience, youmay want to consult one of the Microsoft Solution Providers. SolutionProviders offer a wide range of fee-based services, including creatingcustom macros. For more information about Microsoft Solution Providers,call Microsoft Customer Information Service at (800) 426-9400.

If you know which column is immediately to the right of a vertical pagebreak, or which row is immediately beneath a horizontal page break, you canremove the page break by setting the PageBreak property of the column orrow to xlNone.

For example, to remove a vertical page break to the left of column H, use aline of code similar to the following:
   ActiveSheet.Columns("H").PageBreak = xlNone				
To remove a horizontal page break below row 17, use the following code:
   ActiveSheet.Rows(17).PageBreak = xlNone				
You can also prevent the problem from occurring by entering data either tothe right of or below manual page breaks in worksheets.
Microsoft has confirmed this to be a problem in the Microsoft productslisted at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.
In Microsoft Excel 97, you can use the Delete method of the VPageBreaks andHPageBreaks collections to delete page breaks from worksheets. To use thismethod, it is not necessary to know the exact location of the page break ina worksheet; it is necessary to know only the page break you want to delete(for example, the first, the second, and so on). For example, to delete thesixth vertical page break from the active worksheet, you could use thefollowing line of code:
Note that this method does not work if no data exists to the right of thepage break. Although deleting (or not deleting) such a page break has noeffect when you print the worksheet, you may want to delete extra pagebreaks to keep the worksheets neat in appearance.

Article ID: 170635 - Last Review: 12/04/2015 17:16:59 - Revision: 1.0

Microsoft Excel 97 Standard Edition

  • kbnosurvey kbarchive kberrmsg kbprb KB170635