Page Breaks with VBA

[werhand]

Hello,

I have a sheet with four equal-sized ranges (A2:Z44, A46:Z88, A90:Z132, and A134:Z176), in which the user can hide/show rows of data using Group/Ungroup (the top and bottom line of each range is always visible).

I have a print button attached to VBA code that will set the page setup options and print based on user cell inputs (print 1,2,3 or all 4 of the ranges; portrait or landscape orientation).

What I would like to do is for the code to recognize how many lines are visible in each range, and then decide if and where a page break should be placed between the ranges. Potentially each range could be as short as 23 rows, or as long as 43 rows. So, for example, if each range is 43 rows, it will place a page break at row 89 (midpoint) and break across two pages, or if they each are 23 rows, it won't need to insert a page break and will instead be fit to 1 page wide and 1 page tall. Anybody have an idea? Thanks!

 

[ukmikeb]

Hi

In your module you will have to look at :-

No of pages - ActiveSheet.HPageBreaks.Count

Page Break Location - ActiveSheet.HPageBreaks.Location.Row
where n refers to the page number of the Page Break starting at 1.

Hope that gets you started.

Mike

 

[werhand]

Thanks Mike,

That got me on the right track. I'm trying to delete and relocate the system's page breaks with the following code, but now I run into an error trying to delete a given page break on the first iteration:

For i = 1 To ActiveSheet.HPageBreaks.Count
                Select Case ActiveSheet.HPageBreaks(i).Location.Row
                    Case 1 To 45
                        ActiveSheet.HPageBreaks(i).Delete
                        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A45")
                    Case 46 To 89
                        ActiveSheet.HPageBreaks(i).Delete
                        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A89")
                    Case 90 To 133
                        ActiveSheet.HPageBreaks(i).Delete
                        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A133")
                    Case 134 To 177
                        ActiveSheet.HPageBreaks(i).Delete
                        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Range("A177")
                End Select
            Next i

 

[ukmikeb]

Hi

In my process I tend to set the Page Break earlier than it is with :-

Set ActiveSheet.HPageBreaks(PgCt).Location = Range("D" & NxtLnct)

which does not require the deletion of that specific Break Address or the addition of a new break address.

Very rarely, I get an instance where the final break ends up in mid data. I am guessing that is because I don't cater for a new logical Page break.

It is also worth bearing in mind that the Page Break occurs immediately before the row which is selected.

hth

Mike