I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

Yes, it will if not started from row1

For now, this works with no error.

Sub InsertBreak_At_Change()
Dim i As Long
Range("A1").Select
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord

On Thu, 13 Dec 2007 13:11:00 -0800, Max wrote:

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

Thank you. I worked just fine.

"Gord Dibben" wrote:

Yes, it will if not started from row1

For now, this works with no error.

Sub InsertBreak_At_Change()
Dim i As Long
Range("A1").Select
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord

On Thu, 13 Dec 2007 13:11:00 -0800, Max wrote:

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

The code is working properly but where I have values start next to each other
within column A (Group A - start from A5 that has only one row, Group B -
start from A6)

I need to see the page break between A5 and A6.

Again, Groups that have multiple rows shows up page break properly and
working just fine.

Please provide your suggession/comments.

Thanks.

"Gord Dibben" wrote:

Yes, it will if not started from row1

For now, this works with no error.

Sub InsertBreak_At_Change()
Dim i As Long
Range("A1").Select
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord

On Thu, 13 Dec 2007 13:11:00 -0800, Max wrote:

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

The code is working properly but where I have values within column A starting
from A5 and has only 1 row for the entire group and next group start at A6, I
need to see the page break between A5 and A6.

Example:

Header1(ColumnA) Header2 (ColumnF)
Cell # 5 10/01/2007
------------------------------------------------
Cell # 6 11/01/2007

Groups that have multiple rows show up page break properly with the current
code but in addition to that I need to see the page break between Cell # A5
and A6 as they are different groups.

Please provide your suggession/comments.

Thanks.


"Gord Dibben" wrote:

Yes, it will if not started from row1

For now, this works with no error.

Sub InsertBreak_At_Change()
Dim i As Long
Range("A1").Select
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord

On Thu, 13 Dec 2007 13:11:00 -0800, Max wrote:

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.

Post a sample of your data like you did for your first post because I can't get
a picture from this description.


Gord

On Fri, 14 Dec 2007 07:49:03 -0800, Max wrote:

The code is working properly but where I have values start next to each other
within column A (Group A - start from A5 that has only one row, Group B -
start from A6)

I need to see the page break between A5 and A6.

Again, Groups that have multiple rows shows up page break properly and
working just fine.

Please provide your suggession/comments.

Thanks.

"Gord Dibben" wrote:

Yes, it will if not started from row1

For now, this works with no error.

Sub InsertBreak_At_Change()
Dim i As Long
Range("A1").Select
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord

On Thu, 13 Dec 2007 13:11:00 -0800, Max wrote:

It gives me a debug error :If Selection(i).Row = 1 Then Exit Sub


"Gord Dibben" wrote:

Sub InsertBreak_At_Change()
Dim i As Long
For i = Columns(1).Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) < Selection(i - 1) And IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 12:32:00 -0800, Max wrote:

I have an excel spreadsheet, that I need the page to break when Column A is
not null, after considering the repeated row in the top.

Header1 Header2 Header3
1233454 AAAA $5000
CCCC
______________________
548789 DDDD $1000
KKKK

I want the page to break at the line above. We cant use the pivot table as
its only pulling top raw.