Insert Dynamic Page Breaks with VBA for Excel Macro

Question

I'm running into another roadblock for my VBA knowledge related to a previous post that got resolved late last week.

Excel VBA Cell/Range Selection Loop - Microsoft Community

In the same file that I'm working on cleaning up there are 'custom' page breaks that need to be added based on a value change in a specific column. In column V, we have what is called a 'Bin Group'. When that group number transitions, then I need to insert a page break so that a printed out packet will only contain records for a single Bin Group. The team dealing with this will print the whole sheet, but parse out the paperwork based on Bin Group for multiple users to go perform the actions associated with the data. Generally, the Bin Group values are 9 rows apart, but there are some scenarios where they have more rows between. Then the page breaks need to occur based on Column A being populated.

Bin Group and its values are in Column V. V15 is the first record in the file and should be consistent for all extracts that the Macro will need to run over. The additional rows would come after the Bin (see Column D, Row 20) to extend the distance from each Bin Group indicator.

I feel like it may have some code resembling what was done to solve my previous issue, but things get a little more complex needing to compare values and apply the page break to a different column/row than the comparisons are being done. Screen shots below hopefully will describe the data and what I'm trying to do.

Below, the goldish line is where the Page Break is needed as the Bin Group value changes/transitions from 1 to 2. The purple rectangles above signify the grouping of data that needs to stay together, so the page breaks need to occur prior to the next Part Number (in Column A).

Hopefully, I've provided enough detail to get some suggestions of how to proceed or how to potentially go about a solution. I've struck out on my online searches to this point.

Thanks in advance.

Answer 1

I used constants in column V to identify the values, so it does not pick up blanks per se. You could try adding a page break after the last constant is found
(try it out and adjust the 9 to get the break in the right place)

Sub InsertPageBreaksBasedOnV2()

Dim i As Integer

Dim r As Range

With ActiveSheet

.ResetAllPageBreaks

For i = 3 To .Range("V:V").SpecialCells(xlCellTypeConstants).Areas.Count

If .Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i).Cells(1).Value <> _

.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i - 1).Cells(1).Value Then

.HPageBreaks.Add Before:=.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i).Offset(-1)

End If

Next i

.HPageBreaks.Add Before:=.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i-1).Offset(9) ' Not sure of the offset here

End With

End Sub

Answer 2

HansV, thanks again. I tried both your solution and Bernie's and unfortunately the problems section you started off with includes a few more that were unforeseen in the dataset and vary across instances that the macro will be run. The Bin Group does not always increment by 1. Depending on the product, there may not be certain groups/categories, so it jumps from Bin Group 3 to Bin Group 5, in which case the code you provided missed inserting the page break, which I'm assuming is because of that 'Val(rng.Value) + 1)' since, but I could be wrong there.

The merged cells are a bit of a nuisance I know, not the cleanest output, but a lot of work was done to get things ordered and formatted the way they are, so now we're trying to take things to the next step, which we are very close to having the manual cleanup eliminated or at least cut down to only a few actions.

Thanks again for all the help.

Answer 3

Bernie, thanks for the adjusted code snippet. This worked very well. I think that your IF statement was able to account for the variability in the incrementing of the Bin Group (see what I wrote back to HansV - scenario where Bin Group 4 is skipped). I didn't see any issues with the page breaks where there were Bin Group numbers in column V.

Is there any way to also incorporate a Page Break after Bin Group 9? The remaining records in the report will have a blank Bin Group. They may or may not be categorized in the future, but some of the items don't fall within the other groups, so they get picked separately from the other groupings.

Answer 4

Bernie, one more thing I noticed. In your original suggestion you defined r as a variable, but in your updated response you left that in, but the code no longer referenced that. I wanted to make sure nothing was intended with that variable. I commented it out and ran the macro again and everything seemed to work still.

Answer 5

I did leave that in unnecessarily, so it is okay to take it out.