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
Cartwright@discussions.microsoft.com> 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 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".)



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 Before:= _
> 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 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".)
>
>
>
> 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 Before:= _
> > 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 Before:=FoundCell.Offset(-1, 0)



On Wed, 12 Jan 2005 18:23:01 -0800, "Peter Cartwright"
<PeterCartwright@discussions.microsoft.com> 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 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".)
>>
>>
>>
>> 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 Before:= _
>> > 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