Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Find Page Breaks Faster

  1. #1
    Join Date
    14th September 2006
    Posts
    101

    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 at 00:25.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th September 2006
    Posts
    101

    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).

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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.
    MS MVP - Excel

  5. #5
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Find Page Breaks Faster?

    That's pretty cool, Richard, thanks.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  9. #9
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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.
    MS MVP - Excel

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Demo: Adjust page breaks to control what is printed on each page
    By Dave Hawley in forum Free Microsoft Excel 2007 Tutorials
    Replies: 0
    Last Post: July 26th, 2008, 00:47
  2. Macro: Clear Page Breaks & Add Page Breaks
    By chenm23 in forum EXCEL HELP
    Replies: 7
    Last Post: November 28th, 2006, 05:20
  3. Find Automatic Page Breaks
    By ciumesoz in forum EXCEL HELP
    Replies: 5
    Last Post: October 27th, 2005, 16:58

Bookmarks

Posting Permissions

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