Macro to add a boarder line under my page breaks?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,978
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
Not sure if this is possible?

I have a document with lots of rows of data,
The columns used are B to H

Now the data is set to hide unused rows in excel and everything works great except when i create a pdf if a page breaks in the middle of area it looks wrong as theres on boarder

EXample:

So I have
Data
i here
and its
all part on one section
so theso page break here! for example!boarder bottom row if this?
boarder goes around all these cells
but the page is broken lets say half way
so the pdf doesnot have a boarder at the top and bottom of the pages
so i would like a macro to just adda black boarder bottom row of all page breaks

So in its sim[plest form
if the vba was in engllish it would read

for each row in range B2:H & last row,
if row is a page break row then
Button line boarder in black = true

can anyone help me with this please?

Thanks

Tony
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,285
Not sure I understood your question, anyway the following macro will set the bottom of each horizontal page break with a solid thin black line:
VBA Code:
Sub SetBottomPage()
Dim HPB
'
ActiveWindow.View = xlPageBreakPreview
For Each HPB In ActiveSheet.HPageBreaks
    With Application.Intersect(ActiveSheet.UsedRange, Rows(HPB.Location.Row - 1))
        .Borders.LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).ColorIndex = 0
        .Borders(xlEdgeBottom).TintAndShade = 0
        .Borders(xlEdgeBottom).Weight = xlThin
    End With
Next HPB
ActiveWindow.View = xlNormalView
'
End Sub
Bye
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,978
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Anthony,
that is exacaty what i needed thats very much for your help
Tony
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,254
Messages
5,769,055
Members
425,513
Latest member
9375021758

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top