Re-setting HPageBreaks

[Mike Fogleman]

I have a worksheet where the number of rows changes according to how many
names are added or deleted to the worksheet. I am trying to adjust the
HPageBreaks to accomodate the new range for printout. It seems to work up to
a point for a certain number of rows. My current sheet has row 86 as it's
last row and HPageBreaks(1) is at row 73. I want to move it to row 87 so all
the list will print to one page. I remmed one line that didn't work and
added a line above it, which also doesn't work. The remmed line gives a
runtime error 1004, the new line gives no error, but neither does it move
the page break. Any ideas would be appreciated.
XL 2000-2003

Sub SetHPB()
Dim rng As Range, Hrng As Range
Dim LRow As Long
Dim c As Long
c = Worksheets(1).HPageBreaks.Count
If c = 0 Then Exit Sub
LRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Set rng = Range("A" & LRow)
Set Hrng = Worksheets(1).HPageBreaks(1).Location
If rng.Address = Hrng.Address Then Exit Sub
If Hrng.Row < rng.Row Then
Worksheets(1).HPageBreaks(1).Location =
Worksheets(1).Range(rng.Address)
' Worksheets(1).HPageBreaks(1).DragOff xlDown, 1
Else
Worksheets(1).HPageBreaks(1).DragOff Direction:=xlUp, RegionIndex:=1
End If
End Sub

Mike F

 

[Jim Cone]

Mike,
Excel inserts automatic page breaks if the data won't print on one page
(no matter what).
I would set a print area, specify one page tall and not screw with
the page breaks.
--
Jim Cone
Portland, Oregon USA



"Mike Fogleman"
<[email protected]>
wrote in message
I have a worksheet where the number of rows changes according to how many
names are added or deleted to the worksheet. I am trying to adjust the
HPageBreaks to accomodate the new range for printout. It seems to work up to
a point for a certain number of rows. My current sheet has row 86 as it's
last row and HPageBreaks(1) is at row 73. I want to move it to row 87 so all
the list will print to one page. I remmed one line that didn't work and
added a line above it, which also doesn't work. The remmed line gives a
runtime error 1004, the new line gives no error, but neither does it move
the page break. Any ideas would be appreciated.
XL 2000-2003

Sub SetHPB()
Dim rng As Range, Hrng As Range
Dim LRow As Long
Dim c As Long
c = Worksheets(1).HPageBreaks.Count
If c = 0 Then Exit Sub
LRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Set rng = Range("A" & LRow)
Set Hrng = Worksheets(1).HPageBreaks(1).Location
If rng.Address = Hrng.Address Then Exit Sub
If Hrng.Row < rng.Row Then
Worksheets(1).HPageBreaks(1).Location =
Worksheets(1).Range(rng.Address)
' Worksheets(1).HPageBreaks(1).DragOff xlDown, 1
Else
Worksheets(1).HPageBreaks(1).DragOff Direction:=xlUp, RegionIndex:=1
End If
End Sub

Mike F

 

[Mike Fogleman]

Doing that messes up the vertical page breaks. I have them set so that 10
columns will print per page, for 18 pages, thru column FX. I can manually
drag the horizontal to where I want, but can't seem to do it with code.
Recording a macro doing it manually produces

ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1

which gave the runtime error when done after the worksheet was modified with
the new list. I really need to set the horizontal to the last row + 1 of
column B, in order for the 18 pages to print 10 columns wide each. Any other
suggestions?

Mike F

 

[Jim Cone]

Mike,
You can remove all "manual" page breaks with...
Cells.PageBreak = xlNone
and then place new manual page breaks where you want them...
Rows(87).PageBreak = xlPageBreakManual

However, let me repeat, Excel inserts automatic page breaks
where necessary to fit the data to the page size. You cannot
remove automatic page breaks, you can only change where
they appear by your placement of the manual breaks.

You can change the PageSetup.Zoom setting or change the
row height to make something fit on a page.
So setting a page break at row 87 and setting the zoom to 90% or so
might be something that would work for you.
--
Jim Cone
Portland, Oregon USA



"Mike Fogleman"
<[email protected]>
wrote in message
Doing that messes up the vertical page breaks. I have them set so that 10
columns will print per page, for 18 pages, thru column FX. I can manually
drag the horizontal to where I want, but can't seem to do it with code.
Recording a macro doing it manually produces

ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1

which gave the runtime error when done after the worksheet was modified with
the new list. I really need to set the horizontal to the last row + 1 of
column B, in order for the 18 pages to print 10 columns wide each. Any other
suggestions?

Mike F
"Jim Cone"

 

[Mike Fogleman]

I figured out the runtime problem with the DragOff method. The window must
be in page break view for the DragOff method to have access to the
HPageBreak. Does anyone see a different way to use the DragOff method or
refer to the object it needs without going to PageBreakPreview?

Sub SetHPB()
Dim rng As Range, Hrng As Range
Dim LRow As Long
Dim c As Long
c = Worksheets(1).HPageBreaks.Count
If c = 0 Then Exit Sub
LRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Set rng = Range("A" & LRow)
Set Hrng = Worksheets(1).HPageBreaks(1).Location
If rng.Address = Hrng.Address Then Exit Sub
ActiveWindow.View = xlPageBreakPreview
If Hrng.Row < rng.Row Then
Worksheets(1).HPageBreaks(1).DragOff Direction:=xlDown,
RegionIndex:=1
Else
Worksheets(1).HPageBreaks(1).DragOff Direction:=xlUp, RegionIndex:=1
End If
ActiveWindow.View = xlNormalView
End Sub