Announcement

Collapse
No announcement yet.

Find Page Breaks Faster

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find Page Breaks Faster



    I need to find row numbers of the horizontal page breaks on a sheet quickly. The only method I have found to do this is by using the HPageBreaks property, which is painfully slow.

    Is there another way of doing this that's faster? Or some other manner of using HPageBreaks that improves its speed?

    The scenario is that I have a macro which generates a list of items where each item is two rows long. If an item intersects a page break--one row is on one page and the 2nd row is on the next page--I want to insert a row so that the entire item is on the next page.

    I've been searching everywhere and can't find any other way. Any help would be greatly appreciated.
    Last edited by postman2000; July 21st, 2007, 00:25.

  • #2
    Re: Find Page Breaks Faster?

    You really need to post the code you are using. I suspect switching of the calc and the screen would speed things up.

    You could try jumping to the page breaks like :-

    Code:
    Sub InsertRowPageBreak()
    ' inserts row if on of the cells before and after page break (in column A) are not blank
    Dim ws As Worksheet
    Dim rng As Range
    Dim pb As Variant
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Activate
    ActiveWindow.View = xlPageBreakPreview
    For Each pb In ws.HPageBreaks
        Set rng = ws.Range("A" & pb.Location.Row)
        If rng.Value <> "" And rng.Offset(-1, 0).Value <> "" Then
            rng.Offset(-2, 0).EntireRow.Insert
        End If
    Next pb
    ActiveWindow.View = xlNormalView
    End Sub
    HTH
    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Find Page Breaks Faster?

      That's similar to the code I had. But I tried your code in a fresh workbook, adding the code for turning off screen updating and calculation (and tweaked the row offset for insertting a new row).

      Code:
      Sub InsertRowPageBreak()
      Dim ws As Worksheet
      Dim rng As Range
      Dim pb As Variant
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      ws.Activate
      ActiveWindow.View = xlPageBreakPreview
      For Each pb In ws.HPageBreaks
          Set rng = ws.Range("A" & pb.Location.Row)
          If rng.Value <> "" And rng.Offset(-1, 0).Value <> "" Then
              rng.Offset(-1, 0).EntireRow.Insert
          End If
      Next pb
      ActiveWindow.View = xlNormalView
      
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      
      End Sub
      It took about 12 seconds per 100 rows and each cell in the first column just had two words. That's painfully slow in the application I'm using it in.

      If I have to, I'll use it, but I'm hoping there's another way.....

      Thanks for replying, though!

      Comment


      • #4
        Re: Find Page Breaks Faster?

        Excel needs to chat with the printer driver when you're doing page setup, and I expect this is also true in finding pagebreaks. If the printer is not online, it takes much longer.
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Find Page Breaks Faster?

          It does take time because as shg points out as you run through the pagebreaks collection it starts to talk to the spooler. Your case seems to be too slow though. I tested with 20000 rows and it took 35 seconds (printer off or on)[XP, Office 2003, Core 2 2.13 @ 50%). I have seen several issues with printer drivers slowing Excel to a crawl so maybe worth searching on that or reinstalling.

          As you are waiting have a look in the task manager and see if the spooler (spoolsv.exe) is eating resources.
          locii Insight
          Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

          Comment


          • #6
            Re: Find Page Breaks Faster?

            You can use an XLM4 macro function to return an array of the rows immediately below where the horizontal page breaks are. Unfortunately, despite repeated efforts, I couln't get an array returned in code via Application.ExecuteExcel4Macro() macro - it only seemed to want to return a single value representing the first page break. It did however work for me as a defined name and referencing that in worksheet cells:

            1. Go Insert>Name>Define and call your name something like HPBreaks
            2. In refers to type:

            =GET.DOCUMENT(64)

            3. Click Add
            4. Back in the worksheet select some empty column somewhere (lets say Z1) and type in the formula:

            =INDEX(HPBreaks,ROW())

            and copy down as far as required.

            This will (assuming you have Automatic Calculation enabled) show you on what row the breaks are and will dynamically update. You can refer to this list from code as required ie to incorporate into your existing macro for example.

            Hope this helps!

            Richard

            Comment


            • #7
              Re: Find Page Breaks Faster?

              "XLM4 macro function" ! Nice call Richard. You have obviously been using Excel for a while.

              Unfortunately when I tried it out on the 20K rows it took 20 secs to calculate with spoolsv.exe still active. Inserting rows takes time too. Then I tried adding the ranges together with a Union statement before inserting and it appears to have improved things (Union did not make much difference with the collection method). Now about 25secs.
              locii Insight
              Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

              Comment


              • #8
                Re: Find Page Breaks Faster?

                That's pretty cool, Richard, thanks.
                Entia non sunt multiplicanda sine necessitate.

                Comment


                • #9
                  Re: Find Page Breaks Faster?

                  I wonder if it would be faster compared to accessing the VBA PageBreaks collection if a network printer was involved?
                  You mean maybe the XLM4 function talks to the spooler but maybe not all the way to the printer so if its a network printer the difference maybe more pronounced ? Possible yep.


                  which is pretty slick I think.
                  Me too. That evaluate function is great. There is a post (Aaron's I think) somewhere that lists all sorts that you can do with it.

                  It looks like what ever method we use Excel is going to have a very inefficient conversation with the spooler program. Digging around in the detail of what exactly they are talking about together will take along time. If this was my worksheet I think I would side step the issue and take full control of the printing, page by page, with VBA.
                  locii Insight
                  Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

                  Comment


                  • #10
                    Re: Find Page Breaks Faster?

                    Another way would be to take control and set your own page breaks: use range.height to estimate where the page breaks will occur, and insert a manual break prior at a preferred boundary.
                    Entia non sunt multiplicanda sine necessitate.

                    Comment


                    • #11
                      Re: Find Page Breaks Faster?

                      Thanks for all of your replies! Looks like I have some options to explore. Incidentally, it is a network printer that the computer is hooked up to, so that may be contributing to the delay.

                      I'll dig around and post my results.

                      Thanks again.

                      Comment


                      • #12
                        Re: Find Page Breaks Faster?

                        Try this.
                        Code:
                        Sub SetBreaks()
                            ' points per inch times inches; adjust appropriately according to margins,
                            ' header, and footer
                            Const sngH As Single = 72# * 9#
                            ' lines to keep together
                            Const iGroup As Integer = 2
                            
                            Dim i As Long, j As Long
                            Dim rPA As Range
                            
                            ActiveSheet.ResetAllPageBreaks
                            Set rPA = Range("Print_Area")
                            
                            i = 1
                            j = i + iGroup - 1
                            Do
                                Do
                                    If Range(rPA(i, 1), rPA(j, 1)).Height > sngH Then
                                        ActiveSheet.HPageBreaks.Add Before:=Rows(j - iGroup + 1)
                                        Exit Do
                                    Else
                                        j = j + iGroup
                                    End If
                                Loop While j < rPA.Rows.Count
                                i = j - iGroup + 1
                                j = i + iGroup - 1
                            Loop While i < rPA.Rows.Count
                        End Sub
                        Entia non sunt multiplicanda sine necessitate.

                        Comment


                        • #13
                          Re: Find Page Breaks Faster?

                          What exactly is you are needing to do?

                          Comment


                          • #14
                            Re: Find Page Breaks Faster

                            Set page breaks that break such that groups of lines stay together -- analogous to 'keep with next' in Word.
                            Entia non sunt multiplicanda sine necessitate.

                            Comment


                            • #15


                              Re: Find Page Breaks Faster

                              Thanks for the code, shg!

                              I created an acceptable solution using parsnip's code, but I'm trying your approach to see if it's faster. The only problem I've come across is the Range("Print_Area").

                              For some reason, that range is not created when the PageBreaks are reset. Would having ScreenUpdating turned off or Calculation set to Manual have any affect on that?

                              Thanks again!

                              Comment

                              Working...
                              X