Vba - insert page break

[MRTERMINAL]

Hi,

Can someone help me write a code to insert page break wherever there is an empty row:
eg

column A
ABC
123
456
EMPTY ROW
QWE
RTY
EMPTY ROW

Cheers,

 

[AlphaFrog]

[color=darkblue]Sub[/color] Page_Breaks()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("A:A").SpecialCells(xlCellTypeBlanks)
        ActiveSheet.HPageBreaks.Add Before:=cell
    [color=darkblue]Next[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MRTERMINAL]

Many thanks. That worked.
I also have another workbook where I need to insert page break just below text "Total Cost" (wherever it appears) in column D

 

[AlphaFrog]

[color=darkblue]Sub[/color] Total_Cost_Page_Breaks()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]If[/color] ActiveSheet.FilterMode [color=darkblue]Then[/color] ActiveSheet.ShowAllData
    Cells.PageBreak = xlNone    [color=green]'Delete previous page breaks[/color]
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "Total Cost"
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
        ActiveSheet.HPageBreaks.Add Before:=cell.Offset(1)
    [color=darkblue]Next[/color]
    ActiveSheet.AutoFilterMode = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MRTERMINAL]

You are a genius.Many thanks

 

[Michael M]

Try and keep your questions to the one thread, others may be working on an answer, when it's already been solved.

Duplicate


http://www.mrexcel.com/forum/excel-questions/713220-visual-basic-applications-insert-page-break.html

 

[MRTERMINAL]

apologies. Not sure how this happened.

 

[Michael M]

Ah, sometimes the forum page takes a bit longer to refresh, and it seems the post has disappeared.......then once you repost BOTH of the darn things appear.
Tip for future posts.....If it does happen, just reply to the first post with "Duplicate" and if you need to insert the address of the nerw post as well.....so you don't miss out altogether....