Thread: [Solved] how to repeat row in header
View Single Post
 
Old 06-28-2012, 12:51 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What you could do is submit separate print jobs, such that each range will either have content or be blank. Try the following macro:
Code:
Sub PrintReport()
Dim RwStart As Long, RwEnd As Long, RwLast As Long, StrVal As String
With ActiveSheet
  RwLast = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
  RwStart = 2
  StrVal = Left(.Cells(RwStart, 2).Value, 10)
  For RwEnd = RwStart To RwLast
    If Left(.Cells(RwEnd, 2).Value, 10) <> StrVal Then
      .Range("A" & RwStart & ":I" & RwEnd - 1).Printout
      RwStart = RwEnd
      StrVal = Left(.Cells(RwStart, 2).Value, 10)
    End If
  Next
End With
End Sub
Note: with this code you do not need any page breaks - your 'blank' pages should be deleted and your manual page breaks can be deleted also.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote