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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba page breaks
I assume your range names refer to ranges on the activesheet.
Also, I assume that if rows are hidden, then all the rows in a specific named range would be hidden. Given those assumptions, I would expect it not to count hidden rows. That said, looping through the names collection would not necessarily be the same as traveling down the rows from top to bottom. So there is no guarantee that your count would be meaningful. for example, you first name might refer to rows 1 to 10 and your second name might refer to rows 131 to 140, so with both visible you count would be 20, but that total would have no meaning from a page layout perspective. Why not just loop through the rows from top to bottom and count. If you want to break before a named range, you can add some code to determine where to put the break to meet that rule. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Jesper" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba page breaks
Hi Tom
Sorry, I wasn´t specific enough. All the rows in a specific range aren´t necessarily hidden. Hidden rows are specified in each separate report before I run the macro. So I hope you will help me with another solution. If I name the ranges from top to bottom in alphabetic order it seem like the count is correct except for the above mentioned hidden lines. Regards Jesper "Tom Ogilvy" skrev: I assume your range names refer to ranges on the activesheet. Also, I assume that if rows are hidden, then all the rows in a specific named range would be hidden. Given those assumptions, I would expect it not to count hidden rows. That said, looping through the names collection would not necessarily be the same as traveling down the rows from top to bottom. So there is no guarantee that your count would be meaningful. for example, you first name might refer to rows 1 to 10 and your second name might refer to rows 131 to 140, so with both visible you count would be 20, but that total would have no meaning from a page layout perspective. Why not just loop through the rows from top to bottom and count. If you want to break before a named range, you can add some code to determine where to put the break to meet that rule. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Jesper" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba page breaks
I created a named range RangeName1 and hide some rows in it and left
others unhidden. I then ran this macro: Sub BBB() Dim rw As Range For Each rw In Range("RangeName1").Rows Debug.Print rw.Row, rw.Hidden Next Debug.Print "-----" Debug.Print Range("RangeName1").Rows.Hidden End Sub It produced 3 False 4 False 5 True 6 True 7 True 8 False 9 False 10 False ----- False '<==== this is the result of querying the status of the named range Then I unhid all the rows and hid the first row and ran the macro: 3 True 4 False 5 False 6 False 7 False 8 False 9 False 10 False ----- True so you can see that the test on the defined name is returning the status for the first row in the range. So I don't see any advantage to using the named ranges. -- Regards, Tom Ogilvy "Jesper" wrote in message ... Hi Tom Sorry, I wasn´t specific enough. All the rows in a specific range aren´t necessarily hidden. Hidden rows are specified in each separate report before I run the macro. So I hope you will help me with another solution. If I name the ranges from top to bottom in alphabetic order it seem like the count is correct except for the above mentioned hidden lines. Regards Jesper "Tom Ogilvy" skrev: I assume your range names refer to ranges on the activesheet. Also, I assume that if rows are hidden, then all the rows in a specific named range would be hidden. Given those assumptions, I would expect it not to count hidden rows. That said, looping through the names collection would not necessarily be the same as traveling down the rows from top to bottom. So there is no guarantee that your count would be meaningful. for example, you first name might refer to rows 1 to 10 and your second name might refer to rows 131 to 140, so with both visible you count would be 20, but that total would have no meaning from a page layout perspective. Why not just loop through the rows from top to bottom and count. If you want to break before a named range, you can add some code to determine where to put the break to meet that rule. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Jesper" wrote: 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 |
Reply |
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) |