Forcing page breaks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to get Excel to force page breaks at rows where a cell
contains a certain value? Thank you,
 
Only by you writing a VBA macro to do it.

On Wed, 12 Jan 2005 15:19:02 -0800, "Peter Cartwright" <Peter
 
Thanks Myrna. I suspected that was the case.
VBA is not a strong point of mine. Could you or anyone else help me modify
the following from Frank Kabel of Germany in his post of October 29? He
wrote it to page break every time a value in Column A changed. I need it to
page break every time a value in Column A includes the words "Beginning
Balance".
Here is Frank's code.

Option Explicit
Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "A").Value <> _
Cells(row_index + 1, "A").Value Then
ActiveSheet.HPageBreaks.Add Before:= _
Cells(row_index + 1, "A")
End If
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub


Thank you kindly,
Peter
 
Frank's code loops through all the cells.

You could use instr() (Check VBA's help for syntax/example), but another way is
to find that string and then insert the pagebreak. If you have lots of data,
it's usually lots quicker to find stuff than to search through all the cells
looking.

Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String
Dim WhatToFind As String

WhatToFind = "Beginning Balance"

With Worksheets("sheet1")
.ResetAllPageBreaks 'remove them all to start
With .Range("a:a")
Set FoundCell = .Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
If FoundCell.Row > 1 Then
.Parent.HPageBreaks.Add Before:=FoundCell
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
End With
End Sub

(This puts the pagebreak before the cell containing "beginning balance".)
 
Dave, this is a beauty. Works very nicely.

However I misread my spreadsheet and I need it to page break two rows above
the FoundCell, not one. Can you tell me the expression for that please?

Many thanks,
Peter
 
I have a question regarding the above code.
The find function finds each 'WhatToFind' in the A column and places the page break above it, even if a 'WhatToFind' is in a hidden row.
Is there a way to ignore the 'WhatToFind' items in hidden rows?
 
Back
Top