Excel 2000 - Page break so group of rows not split (VBA?)

  • Thread starter Thread starter WP
  • Start date Start date
W

WP

We are in the process of making a spreadsheet in excel that, depending
on the user's input, will show/hide groups of rows to display only the
relevant information. For example, if user says no to question 1, hide
the rows 2-10. There are approx 20 groups of rows in the spreadsheet.

The problem with the report as it works now is that the page breaks are
positioned directly in the middle of a logical group of rows(ie: rows
all related to the same question). I want the page breaks to fall on
the boundaries of these groups, not in the middle. We have tried adding
a column of merged cells on the left the height of each logical group of
rows, but the page break still splits the merged cell. I am thinking
now that it will have to be done with VBA somehow.

Does anyone have any idea how to position page breaks so that they don't
split a group of rows ?

Thank You
 
Not sure if this answers your question, but it's worth a try.

Go to View>Page Break Preview. You will see each break is a dotted blue
line. Select the rows where you want to put fixed page breaks (at the bottom
of each group of rows you want kept together), right click on the row
numbers and Insert Page Break. Alternatively, drag the existing ones up and
down. These should be represented by solid blue lines, meaning the page
break is 'hard' and will move up and down as you hide the rows.

Paul C,
 
Hate to answer my own question but here'e the VBA code that worked for
me. Quick and dirty but effective. The spreadsheet had several named
ranges with names "Range#". They are hidden or displayed to make a poor
man's dynamic report. This routine forces the page breaks to go in nice
places and not split up the ranges.

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(n).EntireRow.Hidden = False Then
Debug.Print "Processing Range " & n.Name & " with #
rows=" & Range(n).Rows.Count
' add # rows in range to row count.
TtlRows = TtlRows + Range(n).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(n).Rows(1).Address)
'update Totl row count
TtlRows = Range(n).Rows.Count
End If
Else
Debug.Print "Range hidden: " & n.Name & " with # rows="
& Range(n).Rows.Count
End If 'end visible check
End If 'end name check
Next

End Sub
 
Back
Top