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