VBA Printing and Page Breaks

HappyCamper

New Member
Joined
Nov 15, 2005
Messages
3
I'm having a tough time figuring out a way to solve this problem. This is what I have so far:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.PrintTitleColumns = ""
.FitToPagesTall = 400
.FitToPagesWide = 1
.Zoom = False
End With
ActiveSheet.PageSetup.PrintArea = Range("a4:ew" & [a405].End(xlUp).Row).Address
Orientation = xlLandscape

What I have is a varible length spreadsheet that only needs to print data that is filled in. The width varies by users, but I only want it to print one page wide and always on legal paper. Also the rows need to be grouped by two. Basically what happens is that if someone has a different sized column the page break will automatically split one of the rows that is grouped by two. Other times it is not. I was trying to determine the page breaks set up by the above code and if it falls below an even row to move the page break up by one row. Any help would help me save the rest of my hair.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Something like this :-
Code:
Sub AddPageBreaks()
    Dim LastRow As Long
    Dim MyPageLength As Integer
    '---------------------------------------------
    '- remove any existing page breaks
    ActiveSheet.ResetAllPageBreaks
    '- add page breaks
    MyPageLength = 70  ' number of rows to page
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    '- main loop
    For r = MyPageLength To LastRow Step MyPageLength
        With ActiveSheet
            .HPageBreaks.Add Before:=.Cells(r, 1)
        End With
    Next
    MsgBox ("Done")
End Sub
 

HappyCamper

New Member
Joined
Nov 15, 2005
Messages
3
BrianB,

Thanks for the help. Unfortunately it did not do what I was looking for. Is there a way to determine where the page breaks are set up? I was thinking about fiting the info to one page wide and then checking the page break locations. Then based on the location move that page break up on row if it splits on of my data sets.

Thanks,
Chris
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I assumed that your data would be consistent so that pages would be the same length. This would be the best setup.

It is possible to check where existing pagebreaks are, but we cannot "move" them. Only put in a hard break. As you can see, there is more control if we make all breaks hard.

How can we ascertain if the page break is in the right place ? What does the data look like ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,694
Members
414,088
Latest member
rodriboraun

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