---

Excel VBA Macro - Adding Conditional Page Breaks

Got any Excel/VBA Questions? Free Excel Help

DeZaStR needed to write a macro that checks a column of numbers, and each time the number changes, insert a page break. For example, you have a column with all 10's in each row, but on row 25 it then becomes 15's...at that point he wanted a page break.
 
Richie(UK) provided the following code
 
Sub InsertPBs() 
Dim rngMyRange As Range, rngCell As Range 
 
With Worksheets("Sheet1") 
    Set rngMyRange = .Range(.Range("B1"), .Range("B65536").End(xlUp)) 
    'the range to work with 
    For Each rngCell In rngMyRange 
    'loop through the range 
        If rngCell.Value <> rngCell.Offset(1, 0).Value Then 
            .HPageBreaks.Add Before:=rngCell.Offset(1, 0) 
        End If 
    Next 
End With 
 
End Sub
 
Epidemic provided an alternative loop

Sub pagebrk() 
col = 2 ' what ever column you want to measure. 
LastRw = ActiveSheet.UsedRange.Rows.Count 
 For x = 2 To LastRw 
  If Cells(x, col) <> Cells(x - 1, col) Then 
   ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(x, col) 
  End If 
 Next 
End Sub 

DeZaStR noted that both coded solutions worked “great” – Thanks guys.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Excel Date & Time Calculations

Return The Weekday of Date in Excel

Debug VBA Code

Debug, Evaluate/Audit & Fix Excel Formula Errors

Decreasing Validation Lists

Excel Formulas/Formulae, Tips, Tricks and Feature Examples

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Substitutes or analogues of Viagra are available in the form of bluish tablets read the full review here .The active component that helps to cope with erectile dysfunction is sildenafil. The drug increases blood flow to the penis, but men need sexual stimulation. It is usually drunk in a dosage of 50 mg, once. It must be taken 1 hour before the intended sexual act. The maximum allowable dose is 100 mg per day, the minimum is 25 mg.

Gallery

---

• 
• 
• 
• 
• 
•