JimmyTheHand
06-26-2007, 10:57 PM
Hello,
In thread http://www.vbaexpress.com/forum/showthread.php?t=13260 (http://www.vbaexpress.com/forum/showthread.php?t=13260), forum member geekgirlau has provided a very handy little code about pagebreaks. See below.
Sub BottomLine()
Dim ws As Worksheet
Dim pb As HPageBreak
Dim LastRow As Long
For Each ws In ThisWorkbook.Worksheets
ActiveWindow.View = xlPageBreakPreview
For Each pb In ws.HPageBreaks
LastRow = pb.Location.Offset(-1, 0).Row
With ws.Rows(LastRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Next pb
Next ws
End Sub I found that in case the last used row of a worksheet is also the location of the last pagebreak, then the code doesn't work, it gives me Subscript out of range error. See the attachment. I tried a different approach, too, butSet pb = ws.HPageBreaks(ws.HPageBreaks.Count) gives the same error.
What is the cause? How can you overcome it?
Thanks
Jimmy
In thread http://www.vbaexpress.com/forum/showthread.php?t=13260 (http://www.vbaexpress.com/forum/showthread.php?t=13260), forum member geekgirlau has provided a very handy little code about pagebreaks. See below.
Sub BottomLine()
Dim ws As Worksheet
Dim pb As HPageBreak
Dim LastRow As Long
For Each ws In ThisWorkbook.Worksheets
ActiveWindow.View = xlPageBreakPreview
For Each pb In ws.HPageBreaks
LastRow = pb.Location.Offset(-1, 0).Row
With ws.Rows(LastRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Next pb
Next ws
End Sub I found that in case the last used row of a worksheet is also the location of the last pagebreak, then the code doesn't work, it gives me Subscript out of range error. See the attachment. I tried a different approach, too, butSet pb = ws.HPageBreaks(ws.HPageBreaks.Count) gives the same error.
What is the cause? How can you overcome it?
Thanks
Jimmy