It seems this question has been asked on various forums with little or no response. The problem is this. I have a report that generates blocks of data that i want to print out. these data ranges vary in size depending on values in other worksheets. I want to be able to use a macro to insert page breaks at the end of the last full block. i.e. can have multiple ranges per page but not split a single range over multiple pages.

I've got a bit of code that should do the trick but I think it needs a bit of modification.

Sub PB()
Dim lastrow As Long, rngTemp As Range

lastrow = Range("A1").Offset(Rows.Count - 1).End(xlUp).Row
Set rngTemp = Range("A1")

Do While rngTemp.Row <> lastrow
Set rngTemp = Range("A1", rngTemp.Offset(50)).Find(What:="Page_Break", SearchDirection:=xlPrevious)
rngTemp.Parent.HPageBreaks.Add Before:=rngTemp.Offset(1, -7)
Loop
End Sub

if I write "Page_Break" under each range in column A and run this macro I get "Object variable or With block variable not set" message.

Could it be that one of my ranges might be over 50 rows long? In that case the page break should go in the 50th row and split the range. To make things more complicated some of the rows are hidden. How would i go about making sure the code doesnt count the hidden rows?

Any ideas?