Check Criteria to insert Horizontal Page Break Excel.

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
56
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.

VBA 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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

VBA Code:
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
 
Upvote 0
Solution
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.

VBA Code:
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.
 
Upvote 0
You are welcome. I was happy to help.
 
Upvote 0

Forum statistics

Threads
1,218,628
Messages
6,143,589
Members
450,494
Latest member
GolfNut39

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top