Results 1 to 8 of 8

Vba - insert page break

This is a discussion on Vba - insert page break within the Excel Questions forums, part of the Question Forums category; Hi, Can someone help me write a code to insert page break wherever there is an empty row: eg column ...

  1. #1
    New Member
    Join Date
    Nov 2005
    Posts
    35

    Default Vba - insert page break

    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,

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,523

    Default Re: Vba - insert page break

    Code:
    Sub Page_Breaks()
        Dim cell As Range
        For Each cell In Range("A:A").SpecialCells(xlCellTypeBlanks)
            ActiveSheet.HPageBreaks.Add Before:=cell
        Next
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Nov 2005
    Posts
    35

    Default Re: Vba - insert page break

    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

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,523

    Default Re: Vba - insert page break

    Code:
    Sub Total_Cost_Page_Breaks()
        Dim cell As Range
        If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
        Cells.PageBreak = xlNone    'Delete previous page breaks
        Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "Total Cost"
        For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            ActiveSheet.HPageBreaks.Add Before:=cell.Offset(1)
        Next
        ActiveSheet.AutoFilterMode = False
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  5. #5
    New Member
    Join Date
    Nov 2005
    Posts
    35

    Default Re: Vba - insert page break

    You are a genius.Many thanks

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,324

    Default Re: Vba - insert page break

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

    Duplicate


    Vba - insert page break
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    New Member
    Join Date
    Nov 2005
    Posts
    35

    Default Re: Vba - insert page break

    apologies. Not sure how this happened.

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,324

    Default Re: Vba - insert page break

    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....
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com