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?

 

[onlyadrafter]

Hello,

Have you actually got PAGE BREAKS in the spreadsheet? or do you mean you want a horizontal line at the bottom of each page, wherever that may be?

As a workaround can you not put a line of underscores '_' or dashes '-' in the footer?

 

[P Sitaram]

Before printing, you can run this code:

 For Each pgbr In HPageBreaks
    
        With pgbr.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With

    Next

After, printing the borders can be removed by:

  For Each pgbr In HPageBreaks
    
        With pgbr.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom)
            .LineStyle = xlNone
        End With

    Next

 

[Fergus]

Hi onlyadrafter, I have not set pagebreaks, I leave it to work that out automatically based on the margins set. I have tried using a line in the footer successfully in the past, but in this present case the row heights on the spreadsheet vary so the pagebreak is not always in the same place relative to the footer. Other problem of using a line in the footer is that the footer is not wide enough. In this case I need to set my left and right margins at 1cm (A4 landscape print) and the footer cannot draw a line that width. I shall try the suggestion from Psitaram when I get back to my office on Monday and see if I can make that work as it looks like what i need.

 

[Fergus]

Hi psitaram, Thanks for your suggestion, but unfortunately it doesn't work. I have tried various ways based on your code, but none work - it always comes back with a run time error "13" type mismatch. I have tried it with forced pagebreaks and automatic pagebreaks but neither work. I am not much of an expert in vb so now I'm a bit stuck. Any more ideas before I simply manually enter the borders at each pagebreak and change them each time I have to insert a new row? I'm using Office XP Pro.

 

[P Sitaram]

The code has to be placed in the relevant Sheet Module.

I have tested it in Excel 2K/Win 98SR2. It is working fine.

 

[Fergus]

Hi P Sitaram, Thanks again for your reply. I still can't make it work tho. When you say it must be in the relevant sheet module, how do I make that happen? If you have the time let me expand. In the one workbook I have 12 worksheets, each several pages long when printed. To insert the macro for say worksheet 7 then, with sheet 7 opened, I went to the tools menu -> macro -> macros -> typed in a name "lines" -> create and pasted in your code under macro name sub lines() as it came up in the visual basics editor right window. In The visual basics editor left window this then appeared as a separate module at the bottom. i.e. the left window of the editor read:

VBAProject (filename.xls)
Microsoft Excel Objects
Sheet 1
Sheet 2
etc
Sheet 12
This workbook
Modules
Module 1

My code was appearing in Module 1.

I also tried right clicking on Sheet 7 and inserting a new module, but it still appeared in Module 1 at the bottom. So can you tell me how to put the code into the relevant sheet module, although in fact I will eventually want to run the macro for each of the worksheets within the Workbook.

I'll worry about the second macro to remove the lines once I've got the first one working as no doubt that will follow easily.

 

[P Sitaram]

If you right-click on the sheet tab and select `view code' the sheet module will appear.

alternatively, in the vbe, in the `left window of the editor' as you phrased it, you can double-click on the relevant sheet name.

on the other part you are correct i.e., a new sub has to be created with any name you like (eg `lines' as you have already done).

If this works for a sheet, it is easy to convert the code for more general use. That can be tackled once you confirm that the code is working for at least one sheet.

 

[Fergus]

Hi P Sitaram, That's great, thank you so much. First I tried it on one sheet and it worked as you said. I then copied the same code with the same heading "sub line()" into each worksheet's own sheet module. This generally worked so that when, from any worksheet, I click on tools -> macro ->macros I get a whole list of macros, eg Sheet1.line, sheet2.line etc. I have to pick the correct one for that sheet and click on run. On some, but not all, I get a message "Subscript out of range". However, if I set view to pagebreak view and run the macro again it seems to work although for other sheets it works fine in normal view.

Also I am sure there must be a way of making the one macro apply to all the worksheets in the workbook without having to repeat it in each sheet's relevant sheet module - or am I expecting too much?

Two more questions if you are not too busy: Can I limit the extent of cell borders to be drawn, so that rather than running the full width of the worksheet they only run across cells at the pagebreak from say col D to col AH?

I also tried your macro for removing the lines, this worked fine provided that the pagebreaks remained in the same place. If I inserted a couple more rows, then it didn't work as the borders previously drawn were no longer at the pagebreaks. Can I do a macro to remove all horizontal lines from below a certain point, i.e. from below the column headings?

I do appreciate all your help, I'm a great fan of excel generally but have never previously got into vb. In the old excel 2a and even office 97 I used to write my own macro's directly but gave up when MS introduced vb as it seemed too difficult and I had no time to study up on it, but now I'm trying to get back into it and your help has been invaluable

 

[P Sitaram]

1. Place the code in ThisWorkbook Module and prefix the HPageBreaks with ActiveSheet i.e.,

ActiveSheet.HPageBreaks

Now the macro will run on the sheet that is currently active.

2. Use this code to underline selected cells:

With each pgbr in ActiveSheet.HPageBreaks

With pgbr.Location

With Range(.Offset(-1,3),.Offset(-1,33)).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin

End With

End With

End With

Change the second offset numbers to suit your columns.

3. You can select the range of your choice by the format Range(Cell, Cell) and then set the property Borders(xlEdgeBottom) = xlNone. Read up the VBE help on Ranges and how to select them. `UsedRange' property may also be useful.

Happy to Help.