Forcing page breaks
 

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

Is there a way to get Excel to force page breaks at rows where a cell
contains a certain value? Thank you,

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 Befo= _
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 Befo=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".)



Peter Cartwright wrote:

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 Befo= _
Cells(row_index + 1, "A")
End If
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub

Thank you kindly,
Peter

--

Dave Peterson

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

"Dave Peterson" wrote:

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 Befo=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".)



Peter Cartwright wrote:

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 Befo= _
Cells(row_index + 1, "A")
End If
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub

Thank you kindly,
Peter

--

Dave Peterson

Change this line to:

.Parent.HPageBreaks.Add Befo=FoundCell.Offset(-1, 0)



On Wed, 12 Jan 2005 18:23:01 -0800, "Peter Cartwright"
wrote:

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

"Dave Peterson" wrote:

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 Befo=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".)



Peter Cartwright wrote:

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 Befo= _
Cells(row_index + 1, "A")
End If
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub

Thank you kindly,
Peter

--

Dave Peterson

Fantastic Dave. Much appreciated.

That was Myrna.

And I bet she doesn't appreciate the comparison <vbg.

Peter Cartwright wrote:

Fantastic Dave. Much appreciated.

--

Dave Peterson