Calculate number of pages per office

I have a workbook where I'll be printing a large number of sheets. I've programatically set the page breaks, page size and all the headers etc required. The data is sorted by an ID code representing each office. Volumes are variable - some office will have only 5 lines of data, some 400 lines and a variety in between. A blank row separates each line of data. I can work out how many lines each of data each office will have. Is there a way to work out how many pages each office will receive when I print the pages? I had thought of using the HPageBreaks.Count property, but I'm not sure how to link that in with the ID code. How do I work out the number of data lines for each office that exist between each page break?

Thanks

Couple of questions:
1. Are the page breaks keyed off the blank lines?
2. Where is the dept ID stored, the first coloumn?
3. You mentioned page counts first, then you mentioned data line counts? Which are looking for?
4. Once you have this information, where do you want to put it?


Cal

Hi Cal

The Page Breaks are above the blank line where the dept ID changes (breaks within the same dept ID seem to be random)

Dept ID is in Col A

The info can be put in any blank sheet

I would like the to know the number of pages that will be printed for each dept ID - I worked out some code to give me the number of lines of data for each dept.

Thanks, as always, for your help.

Whew,
That was much more difficult then I thought it would be. Here's what you need to do.

1. Setup a worksheet called "Summary"(Or change my code to a sheetname you want to use.)

2. Setup your code to call my commandButton1_click code, or set it up on your own button.

This will only count pages horizontally, so if you have vertical pagebreaks as well, the code will need to be modified.
The worksheet must have a row space between each of the data sets. It will then take the first entry in column A of the section and use that as the summary row name and place the pagebreak count beside it. You can add titles to the summary page.

HTH
Cal

Code:

---

Private Sub CommandButton1_Click()
    Dim Count As Integer, StartRange As Range, EndRange As Range, ws As Worksheet, Pages As String, PrintRange As Range
    Application.DisplayAlerts = False
    Set StartRange = Range("A1")
    If StartRange.Offset(1, 0) = 0 Then
        Set EndRange = StartRange
    Else
        Set EndRange = Range("A1").End(xlDown)
    End If
    ActiveSheet.PageSetup.PrintArea = StartRange.Address & ":" & EndRange.Address
    Do
        Sheets("summary").Range("A65536").End(xlUp).Offset(1, 0).Value = StartRange
        Sheets("Summary").Range("A65536").End(xlUp).Offset(0, 1).Value = CountOfPages(StartRange.Offset(1, 0).Row, EndRange.Row)
        Set StartRange = EndRange.Offset(2, 0)
        If StartRange.Offset(1, 0) = "" Then
            Set EndRange = StartRange
        Else
            Set EndRange = StartRange.End(xlDown)
      End If
      ActiveSheet.PageSetup.PrintArea = StartRange.Address & ":" & EndRange.Address
      If EndRange = "" Then
          Set PrintRange = Range("A1", StartRange.Offset(-2, 0))
          ActiveSheet.PageSetup.PrintArea = PrintRange.Address
          Application.DisplayAlerts = True
          Exit Sub
        End If
    Loop
End Sub

Function CountOfPages(StartRangeRow As Variant, EndRangeRow As Variant)
    Dim iHpBreaks As Integer, PrintRange As Range, cell As Range
    Set PrintRange = Range("A" & StartRangeRow & ":A" & EndRangeRow)
    iHpBreaks = 1
    For Each cell In PrintRange
        If cell.EntireRow.PageBreak = xlPageBreakManual Or cell.EntireRow.PageBreak = xlPageBreakAutomatic Then iHpBreaks = iHpBreaks + 1
    Next
    CountOfPages = iHpBreaks
End Function

---