See if this helps
It is generally agreed that using xlm (xl4) macro commands is better at this
then VBA. Here is one way to do get an array that holds the pagebreak
locations:

Function GetHorizontalPageBreak(sh As Worksheet, wb As Workbook)
Dim horzpbArray As Variant
Dim brkType As String
Dim i As Long
'varr = Evaluate("INDEX(hzPB,COLUMN(A:IV),0)")
wb.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""" & _
sh.Name & """)"
horzpbArray = Application.Evaluate("Index(hzPB,Column(A:IV),0)")
wb.Names("hzPB").Delete
GetHorizontalPageBreak = horzpbArray
End Function

Sub TestHPageBreak()
Dim hpgbrk As Variant
Dim sht As Worksheet
Set sht = ActiveSheet
hpgbrk = GetHorizontalPageBreak(sht, sht.Parent)
For i = UBound(hpgbrk) To LBound(hpgbrk) Step -1
If sht.Rows(hpgbrk(i)).PageBreak = xlPageBreakManual Then
Debug.Print i, hpgbrk(i), "Manual"
Else
Debug.Print i, hpgbrk(i), "Automatic"
End If
Next
End Sub

Regards,
Tom Ogilvy


pcrobinson wrote in message
...
Hi
I need to scan down a column and locate the presence of horizontal page
breaks to add data to the cells immediately below the page breaks. How
programmatically can I know where they are?
Thanks for any help,
Peter Robinson


--
Don Guillett
SalesAid Software

"mik" wrote in message
...
Hello.

I need a function or subroutine that will return an array
of the page ranges on the currently active sheet.

The number of pages is variable, but they always involve
horizontal page breaks only (i.e. no vertical breaks).

They may include both natural breaks (Excel imposed) as
well as manual page breaks (so I can't just count the
number of rows) to the next break.

The print area is always set.

So, as an example, if the set print area is "A3:J111" my
array should contain a list that looks like the following:

1st Element = "A3:J53"
2nd Element = "A54:J105"
3rd Element = "A106:J111"

My attempted code was so bad, I trashed it.

Your example code is what I need. Please help if you can.
Thanks much in advance.