I have an employee scheduling calendar that will dynamically adjust itself
according to the number of employees. How do I get the automatic HPageBreak
to adjust to the last row? For example the previous schedule ended on row
62. That is where the HPageBreak is. After adding some employees the last
row is now 72. Next time there may be fewer employees ending at row 65. How
do I force the HPageBreak to the last row, wherever it is. The VPageBreaks
have already been set and will never change, so I do not want to ReSetAll.

Mike F

i used something like this before, just change for your needs.

If cell.Row Mod 40 = 0 Then
Sht.HPageBreaks.Add befo=cell
End If

--


Gary


"Mike Fogleman" wrote in message
m...
I have an employee scheduling calendar that will dynamically adjust itself
according to the number of employees. How do I get the automatic HPageBreak to
adjust to the last row? For example the previous schedule ended on row 62. That
is where the HPageBreak is. After adding some employees the last row is now 72.
Next time there may be fewer employees ending at row 65. How do I force the
HPageBreak to the last row, wherever it is. The VPageBreaks have already been
set and will never change, so I do not want to ReSetAll.

Mike F

Thanks for the input, but I went with something more complex:

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).DragOff Direction:=xlDown,
RegionIndex:=1
Else
Worksheets(1).HPageBreaks(1).DragOff Direction:=xlUp, RegionIndex:=1
End If
End Sub

Mike F

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i used something like this before, just change for your needs.

If cell.Row Mod 40 = 0 Then
Sht.HPageBreaks.Add befo=cell
End If

--


Gary


"Mike Fogleman" wrote in message
m...
I have an employee scheduling calendar that will dynamically adjust itself
according to the number of employees. How do I get the automatic
HPageBreak to adjust to the last row? For example the previous schedule
ended on row 62. That is where the HPageBreak is. After adding some
employees the last row is now 72. Next time there may be fewer employees
ending at row 65. How do I force the HPageBreak to the last row, wherever
it is. The VPageBreaks have already been set and will never change, so I
do not want to ReSetAll.

Mike F

Mike,
You can adjust automatic breaks as they come where required by your
PageSetup setting.
You can .Delete the HPageBreaks that you set then .add one at the correct
location.

Worksheets(1).HPageBreaks(1).Delete

NickHK

"Mike Fogleman" wrote in message
m...
I have an employee scheduling calendar that will dynamically adjust itself
according to the number of employees. How do I get the automatic
HPageBreak
to adjust to the last row? For example the previous schedule ended on row
62. That is where the HPageBreak is. After adding some employees the last
row is now 72. Next time there may be fewer employees ending at row 65.
How
do I force the HPageBreak to the last row, wherever it is. The VPageBreaks
have already been set and will never change, so I do not want to ReSetAll.

Mike F