Page Break

[Max]

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.

 

[Gord Dibben]

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

 

[Max]

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

 

[Gord Dibben]

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

 

[Max]

Thank you. I worked just fine.

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

 

[Max]

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.

 

[Max]

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]

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


Gord