Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba page breaks
Hi
I have used the below macro to insert page breaks in a report where it is possible to hide different rows. The problem is, that the macro counts all rows in the specific range whether they are hidden or not. Any suggestions to how I can change the macro so it only counts showed rows in the sheet? Sub Sideskift() '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 = 38 'clear any settings that exist Worksheets("Rapport").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 ' 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 Worksheets("Rapport").HPageBreaks.Add befo=Range(Range(n).Rows(1).Address) 'update Totl row count TtlRows = Range(n).Rows.Count End If Else End If 'end visible check End If 'end name check Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't move my page breaks in Page Break Preview | Excel Discussion (Misc queries) | |||
When automatic page breaks are moved each cell becomes a new page | Excel Discussion (Misc queries) | |||
Vertical page breaks won't drag in Page Break Preview | Excel Discussion (Misc queries) | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
Page Breaks- Printing selected rows on same page | Excel Discussion (Misc queries) |