Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.printing,microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 - Page break so group of rows not split (VBA?)
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 |
#2
Posted to microsoft.public.excel.printing,microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 - Page break so group of rows not split (VBA?)
Not sure if this answers your question, but it's worth a try.
Go to ViewPage 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, -- WP wrote: 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 |
#3
Posted to microsoft.public.excel.printing,microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 - Page break so group of rows not split (VBA?)
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 befo=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 WP wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
How to auto keep group of rows together and avoid a page break | Excel Discussion (Misc queries) | |||
Allow rows to break across page in Excel 2007 | Excel Discussion (Misc queries) | |||
can a page break split a cell? | Excel Discussion (Misc queries) | |||
Automatically insert page break every 6 rows in excel doc | Excel Discussion (Misc queries) |