Page Breaks - danielep71/VBA-PERFORMANCE GitHub Wiki

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Class-cPerformanceOptimizer)

Calculating the page breaks after the code executes improves performance.

1. Description

Disabling Page Breaks can help in certain situations:

  • you have previously set a Page Setup property for the relevant worksheet, and your VBA procedure modifies the properties of many rows or columns
  • or your VBA procedure forces Excel to calculate page breaks (displaying Print Preview or modifying Page Setup properties).

VBA code can be slowed down when Excel tries to re-calculate page breaks (note: not all procedures will be impacted).

2. How to use

It is a property of ActiveSheet.

If ActiveSheet.DisplayPageBreaks = False, Excel does not display page breaks.

After your code executes, restore the functionality to its original state.

  • ActiveSheet.DisplayPageBreaks = False at the beginning of your code
  • ActiveSheet.DisplayPageBreaks = True (or original state) before the ending of your code

3. Performance test

4. Results

5. Conclusions

It's not necessary to recalculate page breaks while your code runs.

I recommend hiding the page breaks in virtually all your procedures.

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Class-cPerformanceOptimizer)