Check Criteria to insert Horizontal Page Break Excel.

[hassanleo1987]

Hi,

I am trying to reposition the last horizontal page break in a multipage table based on 2 criteria.

First criteria is to check the column A for a specific value (Lets say 999) and remember the row number of the cell where this criteria fits. (Lets say rcell).

Second criteria is to check whether the last HPageBreak is above or below the location (row) of 1st first criteria cell.

If the last page break is above the (rcell), then it should simply exit the loop / sub but if the last page break is below the (rcell) then it should insert a HPageBreak above (rcell) or offset the last HPageBreak to (rcell) location. (which ever is easier to do!).

So far I have not been successful in making one.

Here is my useless code.

Sub InsertHPageBreaks1()
Dim rCell As Range
Dim hPB As HPageBreak
Dim lRow As Long
Set X = Worksheets(1).HPageBreaks
With hPB = X.Count - 1
 For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
 If rCell = 999 Then lRow = rCell.Row
    If rCell.Row < hPB.Location.Row Then ActiveSheet.HPageBreaks.Add (rCell(2, 1))
    Next rCell
End With
End Sub

Appreciate if somebody can help!

 

[igold]

You did not say where you were getting the 999 value from for your criteria, so I just hard-coded it as the rCell value to search for. I hope that makes sense to you. The code will search for 999 and pick up the row number and then fill your requirements.

Sub InsertHPageBreaks1()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rw As Long, lb As Long, rCell As Long
    
    rCell = 999       '*** Hard-Coded Here****
    rw = ws.UsedRange.Find(what:=rCell).Row
    ws.ResetAllPageBreaks
    lb = ws.HPageBreaks.Item(ws.HPageBreaks.Count).Location.Row
    If rw > lb Then Exit Sub
    ws.Rows(rw).PageBreak = xlPageBreakManual
    
End Sub

 

[hassanleo1987]

You did not say where you were getting the 999 value from for your criteria, so I just hard-coded it as the rCell value to search for. I hope that makes sense to you. The code will search for 999 and pick up the row number and then fill your requirements.

Sub InsertHPageBreaks1()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rw As Long, lb As Long, rCell As Long
   
    rCell = 999       '*** Hard-Coded Here****
    rw = ws.UsedRange.Find(what:=rCell).Row
    ws.ResetAllPageBreaks
    lb = ws.HPageBreaks.Item(ws.HPageBreaks.Count).Location.Row
    If rw > lb Then Exit Sub
    ws.Rows(rw).PageBreak = xlPageBreakManual
   
End Sub

The rcell value is OK to be hard coded because it is just for reference.
Thanks a lot @igold for your help, It works perfectly for my application.

 

[igold]

You are welcome. I was happy to help.