Page break

[Per_]

Hello,

Is it possible in Excel that let you define an area that is not page breaked ?

Per

 

[FishingIsMoreFun]

Look under options.

My version of Excel has a tab with an option called "Page Break".

You can either check or un-check the box.

I guess un-checking it will eliminate the use of page Breaks.

You can also redefine the page break area under view.

 

[Per_]

I would like to do it automatic ... define no page break between two names ...

 

[FishingIsMoreFun]

Well this small macro will alter the page break area.
My my version it works and you do not have to go into another view.

This changes the page break to range("$A$1:$I$24").

Try it and see if it works.

Sub ChangePageBreak()
   
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$24"
    
End Sub

 

[FishingIsMoreFun]

Mind you the page break appears to be associated with print area so you are limited by paper size.

cheers

 

[Per_]

Thanks!!! I will se if I can manage what I want to do ... This is what I want to do.

I have a sheet that can change, people can add/remove lines by pushing buttons

Header 1
Text1
Text2
Test3
<---- Would like to get the page break here
Header 2
Test1
Test2
Test3

I would like to prevent to get a page break wihin the each group, so I would like the page break to come after Header 1 and Text 3.

 

[FishingIsMoreFun]

So you want the page break to shrink and expand with the list?

You could add a row and put the text "page break" in a cell (then white it out).

That would give you a consistent cell to refer to in some code.
As long as someone does not delete it.

Then you could refer to that cell in code.

Other than that the ranges would change and you would need to define the break relative to the second header as a cut off maybe.
Or end of data (though end of data would stop at a blank cell).

Just a few ideas.

Cheers

 

[Per_]

That is what I try to do just now Thanks for the help!

 

[Per_]

How to reset the HPageBreaks page break ? Back to default values ?

 

[FishingIsMoreFun]

Ok here is something that may you a bit.
I am assuming you have A4 paper and you only need to change the
horizontal page break.

A4 ends roughly at column I, but if you only need a horizontal break then it doesn't matter.

I entered the text "Page Break" into a cell in column "I", the text will move up and down as the rows shrinks and expands.

This code simply looks through column "I" and then finds the "Page Break" cell.

The code then adds the address of that cell to the page break address on the new line.



Warning: If the code does not find "Page Break" the code will go into a never ending loop,
so you might want to add something to stop it if it doesn't find the cell (so if someone removes a column the code will not find it).

Not the most eloquent of solutions but might get you started.

Cheers

Sub ChangePageBreak()

   Application.ScreenUpdating = False
   Range("I1").Select
   
   Do Until ActiveCell.Value = "Page Break"
   ActiveCell.Offset(1, 0).Select
   Loop
   pg = ActiveCell.Address

     'MsgBox CStr(pg)

   ActiveSheet.PageSetup.PrintArea = "$A$1:" + pg
    
End Sub

oh yeah and the msgbox is commented out, but its good to use just to see what the code is grabbing (or use the "Locals Window" and step through the code with F8).