Macro performance slow when page breaks are visible in Excel

[Adamaris]

Hi friends,
I usually work on the Excel and I have observed that when I use the page breaks the performance of an Excel slows down. I have tried to do lot of different things but still the performance slows down after using the page breaks. So I thought to take some suggestions from you guys.!! Please explain me about the Macro performance slow when page breaks are visible in Excel Hope that you understood my problem.!?

[Rob Dizzle]

When you use a Microsoft Visual Basic for Applications (VBA) macro in a later version of Microsoft Excel, the macro will take longer to complete than in earlier versions of Excel. For example, a macro that takes several seconds to complete on an earlier version of Excel may take several minutes to complete in a later version of Excel. Alternatively, if you run a macro a second time, the macro can take twice to run as did the first time.

[DANIEL 602]

Even I have observed that the performance of a macro slows down after using the page breaks in Excel. And I have concluded that this problem can occur if the following conditions takes place :

1. The VBA macro changes the properties of many rows or columns.
2. In Excel 2003 and earlier versions of Excel, select the check box page breaks on the View tab in the Options dialog box.

Hope that you would get some help from this post.

[Bigga Lexx]

The issue that you have discussed can also occur if you have performed an operation that forces Excel to calculate page breaks. Excel calculates the page breaks when you perform any of the following :

1. Show a preview of the worksheet.
2. Click Microsoft Office Excel 2003 and earlier versions of Excel, Page Setup on the File menu. In Microsoft Office Excel 2007, click Page Setup on the toolbar shortcut.
3. Modify any of the PageSetup object properties in a VBA macro.

[Kirmiac]

In Excel 2003 and earlier versions of Excel, select the check box page breaks on the View tab in the Options dialog box. Whereas in Excel 2007, click the Microsoft Office Button, click Excel Options, click the Advanced category and click to select the Show page breaks in the Display section options for this page. This problem occurs if the macro sets PageSetup any property or if you manually set a property PageSetup and then run a VBA macro that sets properties on a large range of rows or columns. When you set a property PageSetup, automatic calculation of page breaks are enabled in Excel. By default, this property is not set.

[chroma]

To disable the display of page breaks programmatically, add the following line of code to the VBA macro :

ActiveSheet.DisplayPageBreaks = False

You will have to keep in mind that you must insert this line of code after modifying any object properties PageSetup, but before you change the properties or rows in the spreadsheet.