Here is some sample code I posted previously. Pagebreaks is pretty slow, so
I use the Excel4 macro approach here which is somewhat faster. The output
is to the immediate window. This will provide a basic approach to
discovering where your pages are - you can then add code to create borders
if you wish.
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.
