Can you get the range reference for each page in a worksheet print range?

[Crosby]

Is there a way to determine the range for each of the
pages (by pages I mean the pages as they would print out)
on a worksheet. I would like to build a new printrange
based on printing only the pages I want to print.

 

[Tom Ogilvy]

loop through the vertical and horizontal pagebreaks collections possibly.

 

[Guest]

Good idea. I am going to try that. I'm sure I can get the
row component of the page that way, but how about the
column component. I assume if there is only one column of
pages, there wouldn't be any vertical page breaks.

 

[Tom Ogilvy]

You can use the UsedRange to determine both the horizontal and vertical
extent of what Excel considers to be in use. If you have set a printarea,
then you can use that to determine the extent of your printarea.

Yes, the pagebreak collections can be difficult to deal with. Also note
that they (automatic pagebreaks) can change with a change in which
printdriver is in effect.

Here is some rough code that I have posted in the past which might give you
a start. It uses some Excel4 macro commands because those are faster and
more dependable:


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