Horizontal Cell Borders at Page Breaks

[Fergus]

I have a long worksheet with only the vertical cell borders shown such that when printed it appears as a number of columns. When I print it (with column headings repeated) I would like to generate a horizontal border across the last cells before the page break to clean up the appearance. The page breaks are not forced, just depend on the margin settings. The positions of page breaks will vary as at times there is a need to insert new rows into the worksheet at different locations. I know I can easily do this job manually in page break view by setting the borders but does anyone know how this can be automated?

 

[Fergus]

Hi again P. Sitaram and thanks for your last help. Items 1 & 2 I have now got to work fine, so thanks a lot, but I am having big problems trying to write a code to delete any horizontal cell borders below rows 1- 7 which are repeated header rows at the top of each page and I don't want to delete any cell borders in the column headings. The width of each worksheet is from cols D to AH (cols A,B & C are hidden). The maxiumum length of any of the 12 worksheets is less than 150 rows. The latest bit of code I have come up with is:

Sub noline()

For Each c In Activbesheet.HPageBreaks

With c.Location
With Range("D7:AH150").Borders(xlEdgeBottom)
.LineStyle = xlNone
End With
End With

Next

End Sub


But this clearly isn't working and now I'm lost. I did read up on Ranges and all sorts of other topics too and have tried to adapt and/or combine many different examples given in the help files but all without success so far. Have you any more advice? - or shall I revert to doing it manually?

 

[P Sitaram]

Try:

For Each rw In Range("D7:AH150").Rows
    
    rw.Borders(xlEdgeBottom).LineStyle = xlNone
Next