What is **FASTEST** way to see if RANGE contains a PAGE BREAK???

[Alan Mailer]

In a message I posted about a week ago, I asked how to discern whether
a particular range contained a Page Break (be it manual or custom).

I got a helpful answer suggesting that I loop through the rows in the
range and keep testing for:

r.PageBreak <> xlPageBreakNone

This has worked fine... but I have noticed that it is somewhat slow;
even when the range is only 100 rows or less.

Without showing any ingratitude to the person who suggested the above
solution... does anybody know of a FASTER way to go about this?

Thanks in advance.

[Joey K.]

Did you use a Range(cell).select in your loop? In which case you will
actually see the loop in action? If so, I recommend you use
Application.screenupdating = false.

Hope this helps.

Joey

"Alan Mailer" <clarit...@earthlink.net> wrote in message
news:hfevaukpquojbcaea...@4ax.com...

[Tom Ogilvy]

I posted this previously as indicated:

http://groups.google.com/groups?selm=uzgo0D7q%23GA.237%40cppssbbsa03&output=
gplain

From: "Thomas Ogilvy" <twog...@email.msn.com>
Subject: Re: need page break macro help
Date: 1999/05/31
Message-ID: <uzgo0D7q#GA.237@cppssbbsa03>#1/1
References: <3752c...@news.a-znet.com>
Newsgroups: microsoft.public.excel.programming
X-Mimeole: Produced By Microsoft MimeOLE V4.72.3110.3

This is based on a technique posted by Bob Umlas

Here is a method to get an array of horizontal pagebreaks and vertical
pagebreaks. The horizontal pagebreaks are a list of rows that have the
pagebreak and vertical a list of column numbers:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print J, horzpbArray(J)
Next J

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For J = LBound(verpbArray, 1) To UBound(verpbArray, 1)
Debug.Print J, verpbArray(J)
Next J
End Sub

This uses an Excel 4 macro to get this information. This is much faster
than the VBA pagebreak which uses the printer driver and can be very slow.

The is a pagebreak property of the range. It can be tested to see if a
pagebreak exists

if rows(6).pagebreak = xlNone then
'No pagebreak
Else
' Has pagebreak
if rows(6).pagebreak = xlPageBreakAutomatic then
'Automatic pagebreak
elseif rows(6).pagebreak = xlPageBreakManual then
' Manual pagebreak
End if
End if

Combining the above gives:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
If Rows(horzpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, horzpbArray(j), brkType
Next j

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
If Columns(verpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, verpbArray(j), brkType
Next j
End Sub

Sample Output:
Horizontal Pagebreaks (rows):
1 13 Manual
2 24 Manual
3 39 Manual
4 67 Manual
5 87 Manual
6 114 Automatic
Vertical Pagebreaks (columns):
1 2 Manual
2 6 Automatic

This should get you started.

Regards,
Tom Ogilvy

Alan Mailer <clarit...@earthlink.net> wrote in message
news:hfevaukpquojbcaea...@4ax.com...

[dcrom...@juno.com]

Tom O wrote:
> .. Here is a method to get an array of horizontal pagebreaks and vertical
>pagebreaks...
>..

>ThisWorkbook.Names.Add Name:="hzPB", _
> RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"

>..

> While Not IsError(Evaluate("Index(hzPB," & i & ")"))

>..
>This uses an Excel 4 macro to get this information. ..

That was a neat routine, except my computer locked up with
"Out of Memory" after using it. I found that the following
locks up my computer. I have Excel97 SR1. I have 96MB
RAM. Have you heard of this problem? Thanks for your stuff. Dave

Sub DemoMemoryProb()
' Warning -- this may lock up your computer with "Out of Memory"
Dim i&
For i = 1 To 10000 ' My 98MB machine locks up at under 3500
cells(1, 1) = i
cells(1, 2) = ExecuteExcel4Macro("get.document(50)") ' number of pages
DoEvents
Next i
End Sub

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----