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
"Mike Fogleman" 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" wrote in message
...
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"

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