Counting Manual Page Breaks Only

[Kevin R]

I have a very long spreadsheet where I've used vba to reset all page breaks
and then manually insert page breaks before key cells. I'm trying now to
count the number of manual page breaks using ActiveSheet.HPageBreaks.Count
but it appears to be counting both the manual and automatic (those reinserted
by excell between the manual breaks). Is there a way around this??

 

[Rick Rothstein]

This function should return the value you want...

Function NumberOfManualPageBreaks(Optional WS As Worksheet) As Long
Dim HP As HPageBreak
If WS Is Nothing Then Set WS = ActiveSheet
For Each HP In WS.HPageBreaks
If HP.Type = xlPageBreakManual Then
NumberOfManualPageBreaks = NumberOfManualPageBreaks + 1
End If
Next
End Function

Simply pass it a reference to the worksheet whose manual horizontal page
breaks you want to count or omit the argument completely to return the
manual horizontal page break count for the active sheet.