Bottom Line in every page

p45cal · #1 08-16-2022, 01:14 PM

Untested, try:

Code:

Sub BottomLine2()
Dim ws As Worksheet
Dim pb As HPageBreak
Dim LastRow As Long


For Each ws In ThisWorkbook.Worksheets
  origview = ActiveWindow.View
  ActiveWindow.View = xlPageBreakPreview

  For Each pb In ws.HPageBreaks
    LastRow = pb.Location.Offset(-1, 0).Row
    With ws.Range("B" & LastRow & ":H" & LastRow).Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThin
    End With
  Next pb
  ActiveWindow.View = origview
  'ws.DisplayPageBreaks = False
Next ws
End Sub

There's a commented out line you can add if you find you have unwanted pagebreaks showing.

Marcia · #2 08-18-2022, 08:26 AM

Thank you p45Cal.
There are issues when I pasted the code to the actual data sheet.
1. The code took several seconds to do its job and the sheet was "dancing" during execution. The actual sheet has several columns populated with formulae. Only columns B to H need to be printed in a report that is why I only showed cols B to H in the mock up data.
2. After running the code, the sheet (actual) ended in a page break view with a message, "subscript out of range." Though when I shifted the sheet to normal, the bottom lines were there.
3. When the page break has a row hidden below it, there was no line in the page break (row 121).

p45cal · #3 08-19-2022, 06:10 AM

Quote:

Originally Posted by Marcia

1. The code took several seconds to do its job and the sheet was "dancing" during execution. The actual sheet has several columns populated with formulae.

I've put Application.ScreenUpdating lines to try and remove that. If it's still a bit slow, come back.

Quote:

Originally Posted by Marcia

2. After running the code, the sheet (actual) ended in a page break view with a message, "subscript out of range." Though when I shifted the sheet to normal, the bottom lines were there.

That hasn't happened here; what line did it report was at fault?
If it comes up with an error and you click End, it will not execute the rest of the code which would have put things as you want.

Quote:

Originally Posted by Marcia

3. When the page break has a row hidden below it, there was no line in the page break (row 121).

This was the most difficult to solve and the code has been tweaked accordingly.
The reason no line was showing was that your code found the page break (which is the cell that is printed at the top of the next page) and added a bottom-of-cell line to the row above it. This is OK most of the time, until the row above the page break is also hidden, so any borders to that row aren't seen. I had to move back up, row by row, looking for the next visible row above the page break and added the line to that.

There are quite a lot of changes to the code:
Moved it to a standard code-module (having it in a sheet's code-module risks you losing it should you delete the sheet, and anyway, it makes no sense to have in a sheet's code module when it's working on multiple sheets).

Added a bit to embellish the Totals row.

For the code to work, the code has to switch the sheet it's working on to page break preview view, and for that to happen the sheet needs to be the active sheet. So while that happens, there's a danger that the sheet which was the active sheet before the macro runs is not the same as the one that is active after it has run. That has been addressed.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Table styles - First column shading with bottom line on page break	NicB	Word Tables	2	02-20-2019 03:05 AM
Paginate table so cell headings appear top of page instead bottom page when no room bottom page	mtcn	Word Tables	5	12-11-2014 12:49 PM
get rid of line above page numbers at bottom of pages.	Joanrichmond	Word	1	02-11-2014 12:57 PM
line and ?object at bottom of page in word 2007 doc	btdaniel	Word	10	07-23-2012 03:03 AM
Horizontal line at bottom and top each page	BrutalLogiC	Word	1	07-03-2011 02:22 AM