vba page breaks

[Guest]

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 before:=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

 

[Guest]

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.

 

[Guest]

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:

 

[Tom Ogilvy]

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.