Excel Macro Page break insertion issue

---

Excel 2016
I have a worksheet that is composed of 928 individual packing lists. I am stumped on writing a "do while" formula in macros to search for " Packing List" and then insert a page break before the active cell. I have looked everywhere online and even went and bought excel for dummies and the formulas and functions for dummies! All to no avail. On the sheet I went to Column I and wrote a formula to count all "Packing List" within the worksheet. then wrote the following script.

Option Explicit

Sub InsertPageBreaks()

Dim PackingList As Integer
Dim i As Long
Dim r As Long

PackingList = Range("i1").Value
i = 2
r = 2

Do While i <= PackingList
If Cells(r, 1).Value = "Packing List" Then

Rows(r).PageBreak = xlPageBreakManual
i = i + 1
End If
r = r + 1
Loop

End Sub
It errors out.

Please someone point me to my errors!
Thank you
Paul

@Paul-Arthur ,

Any chance you can upload a copy of your workbook? Maybe just a screenshot, I'm having trouble visualizing what you are trying to do.

@Paul-Arthur ,

The problem is the variable r continues to increment until it reaches 1048577. The last row in a spreadsheet is 1048576.

There are several ways you could fix this issue.

You could use a second condition in your do while statement.

This would prevent the error, but does your script really need to check over 1 million rows? I don't know how big your data set is, and it takes longer to execute.

Another option would be instead of counting the number of instances of "Packing List" and using that as the loop counter, you could count the number of rows of data and use that to limit the loop.

This way you can eliminate a couple of variables, simplify the code, and have it execute faster.

Let me know if this works, hard to say without seeing your data. I used this data set for my debugging.

PS If anyone is curious, I used the debug and step into options in the VBA editor to find the problem with the initial sub. It allows you to hover over variables and see their values as the loop progresses, and the value of the variables at the time of the error.