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

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 3

HansV, can you try this link to OneDrive: ToCommunity

I have a raw Excel extract of the report output that I'm trying to clean up and the Macro code copied into a .txt file. Let me know if that works, otherwise I'll look for an alternate solution to share.

Thanks again for all your support. Very appreciated.

Answer 4

There were two problems:

1. The values 1, 2 etc. in column V look like numbers, but they are text values.
2. These values are in merged cells. VBA doesn't play well with merged cells.

Workaround:

Dim rng As Range  
Set rng = Range("V15")  
Do  
    Set rng = Range("V:AD").Find(What:=CStr(Val(rng.Value) + 1), After:=rng, LookIn:=xlValues, LookAt:=xlWhole)  
    If rng Is Nothing Then Exit Do  
    ActiveSheet.HPageBreaks.Add Before:=rng.Offset(-1)  
Loop

Answer 5

Try it like this:

Sub InsertPageBreaksBasedOnV()

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 

End With 

End Sub