Return array of page ranges

[XP]

Using Windows XP and Office 2003.

Hi, I need a function that will retrieve the range address of each defined
page range on a sheet.

For example, if page one is set to A2:H50, and page two is set to A51:H80, I
need to retrieve each of these sets of ranges. The number of pages may vary
at different times. I would prefer to work with an array if possible.

Your assistance in the form of example code would be greatly appreciated.

 

[Jim Thomlinson]

Page ranges??? Do you mean print ranges or named ranges or ???

 

[XP]

Sorry, yes, a defined print range that may include one to any number of
pages arranged vertically on the sheet.

If you switch the sheet to page break preview you can see where the page
range is defined and where the breaks will occur outline in a solid blue line.

I need an array of each of those page ranges...e.g. A2:H43, A44:H87, etc.

Thanks...

 

[Jim Thomlinson]

Here is the location of the page breaks... I see that the print area you have
is A2 to ??? Is it a defined print area or is it just everything on the sheet?

Dim lngPageBreaks() As Long
Dim lng As Long
Dim hpb As HPageBreak
Dim wks As Worksheet

Set wks = ActiveSheet

For Each hpb In wks.HPageBreaks
ReDim Preserve lngPageBreaks(lng)
lngPageBreaks(lng) = hpb.Location
lng = lng + 1
Next hpb

For lng = LBound(lngPageBreaks) To UBound(lngPageBreaks)
MsgBox lngPageBreaks(lng)
Next lng

 

[Héctor Miguel]

hi, ?

AFAIK, what you need could suffer variations depending on non/controlled events
(i.e. changing page-setup/printer resolution/quality, pint area-titles, printing order, and so on)

i guess you could seek for options/alternates in the following pages:

Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Build TOC Another Approach
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm

regards,
hector.

__ OP __