Array of page ranges

[mik]

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.

 

[Don Guillett]

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

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

 

[Chip Pearson]

Mik,

Try something like the following:

Sub AAA()
Dim SRng As Range
Dim ERng As Range
Dim HPB As HPageBreak
Dim WS As Worksheet: Set WS = ActiveSheet
Dim Arr() As Range
Dim Ndx As Long: Ndx = 1

ReDim Arr(1 To WS.HPageBreaks.Count + 1)
Set SRng = Range("A1")
For Each HPB In WS.HPageBreaks
Set ERng = HPB.Location(0, 1)
Set Arr(Ndx) = Range(SRng, ERng)
Set SRng = HPB.Location
Ndx = Ndx + 1
Next HPB
Set Arr(UBound(Arr)) = Range(ERng(2, 1), Cells(Rows.Count, "A"))
For Ndx = LBound(Arr) To UBound(Arr)
Debug.Print Arr(Ndx).Address
Next Ndx
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Pete McCosh]

You can use the Hpagebreaks collection to do this.

X = Activesheet.HpageBreaks.Count

will give you the number of page breaks. You can then
cycle through the collection and get the addresses of the
top left cell on each page using:

Activesheet.HPageBreaks(X).Location.Address

 

[Mik]

Okay, I will pick these suggestions apart and am confident
I can come up with a solution. Thanks much to all who
responded.