Expand/Collapse groups based on value and auto page break after group not through it.

[Lonemascot]

2 part question

Q1) I have sheet which is made up of pricing elements of a job. If an element is priced at £0 then then it doesn't need to appear on the report and I'd like to collapse the group automatically as opposed to clicking each one individually before printing.

I've used the following code: (where D21 is the price total and rows 9:20 are the details of the element being priced.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Range("$d1:d700")) Is Nothing Then


If Range("d21").Value = 0 Then
Rows("9:20").EntireRow.Hidden = True
Else
Rows("9:20").EntireRow.Hidden = False
End If

End If


End Sub

This works lovely until I insert an additional row within the range and the VBA code doesn't update to match the new range.

Is there a way to either do this within Excel as part of the grouping function or use a different VBA code to take account for inserting additional rows within the range?

Q2) How do I set page breaks to only break at the end of a group rather than in the middle of it? I looked at the subtotal function but seeing as my data isn't in a format where there are column headings I can't see how I could get that to work.

 

[Lonemascot]

Ok I've done a bit digging and found the following code from a historic post back in 2003.

I've highlighted my groups as Named Ranges and called each range something that starts with "Range_####".

Private Sub CommandButton1_Click()
'Change pages breaks so it is only split on a named range top or bottom
Dim n As Name
Dim RngRows() As Integer
Dim PageBreakRows() As Integer
Dim NumPageBreaks As Integer
Dim i As Integer
Dim TtlRows As Integer
Dim MaxRowsPerPage As Integer
Dim strBreakRow As String
Dim intBreakRow As Integer


i = 1
TtlRows = 0
NumPageBreaks = 0
MaxRowsPerPage = 50


'clear any settings that exist
ActiveSheet.ResetAllPageBreaks


For Each n In ActiveWorkbook.Names


'operate only on ranges that start with RANGE in their name
If Mid(n.Name, 1, 5) = "Range" Then


'if named range is visible
If Range.EntireRow.Hidden = False Then
Debug.Print "Processing Range " & n.Name & " with # "
Rows = " & Range.Rows.Count "
' add # rows in range to row count.
TtlRows = TtlRows + Range.Rows.Count
'if rount count larger than max allowable add a page break
If TtlRows > MaxRowsPerPage Then
'take first row of current named range and add page break
ActiveSheet.HPageBreaks.Add
before:=Range(Range.Rows(1).Address)
'update Totl row count
TtlRows = Range.Rows.Count
End If
Else
Debug.Print "Range hidden: " & n.Name & " with # rows="
& Range.Rows.Count
End If 'end visible check
End If 'end name check
Next


End Sub



However, i cant get it to work. Any obvious mistakes?