Results 1 to 4 of 4

Page Breaks with VBA

This is a discussion on Page Breaks with VBA within the Excel Questions forums, part of the Question Forums category; 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 ...

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    5

    Default Page Breaks with VBA

    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!

  2. #2
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    1,865

    Default Re: Page Breaks with VBA

    Hi

    In your module you will have to look at :-

    No of pages - ActiveSheet.HPageBreaks.Count

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

    Hope that gets you started.

    Mike

  3. #3
    New Member
    Join Date
    Jun 2009
    Posts
    5

    Default Re: Page Breaks with VBA

    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:

    Code:
    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

  4. #4
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    1,865

    Default Re: Page Breaks with VBA

    Hi

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

    Code:
    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
    Last edited by ukmikeb; Apr 29th, 2010 at 06:33 PM. Reason: added text

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com