Inserting page breaks with VBA

Question

I have the following piece of code given by HansV.  It is great and I use it all the time.  What I'm now looking for is a way to do kind of the same thing where the code says to insert a page break above row 44, then go down 21 rows and insert a page break, then down another 21 rows and insert a page break until it reaches row 1221.  Does anyone have any ideas how to do this?  NOTE:  I have modified the following code to run as a macro by inserting the ranges into the variables, but this is the way the code came from HansV.

Sub Vari_Copy()

' This sub allows for manually coping and pasting a range

Dim MyRange As Range

Dim StepValue As Long

Dim StopRow As Long

Dim Col As Long, X As Long

Set MyRange = Application.InputBox(Prompt:= _

                "Please select a range to copy with your Mouse.", _

                    Title:="Specify range", Type:=8)

Col = MyRange.Cells(1).Column

StepValue = Application.InputBox(Prompt:= _

                "Enter Step value.", _

                    Title:="Step Value", Type:=1)

StopRow = Application.InputBox(Prompt:= _

                "Enter stop row.", _

                    Title:="Stop Row", Type:=1)

For X = MyRange.Row + StepValue To StopRow Step StepValue

    MyRange.Copy

    Cells(X, Col).PasteSpecial

Next

Application.CutCopyMode = False

End Sub

Answer 1

There are probably more efficient ways to do this, but here is one version:

Sub DeleteRows()

    Dim lngRow As Long

    Dim lngStart As Long

    Dim lngStop As Long

    Dim lngStep As Long

    lngStart = 44

    lngStep = 21

    lngStop = 1221

    lngRow = lngStart

    Application.ScreenUpdating = False

    Do While lngRow <= lngStop

        Range("A" & lngRow).EntireRow.Delete

        lngRow = lngRow + lngStep

        lngStop = lngStop - 1

    Loop

    Application.ScreenUpdating = True

End Sub

Answer 2

I'm not sure I understand your request, but does this do what you want?

Sub InsertPageBreaks()

    Dim lngStart As Long

    Dim lngStep As Long

    Dim lngStop As Long

    Dim lngRow As Long

    ' Ask the user for input

    lngStart = Application.InputBox(Prompt:= _

        "Please enter the start row", Type:=1)

    lngStep = Application.InputBox(Prompt:= _

        "Please enter the number of rows to skip", Type:=1)

    lngStop = Application.InputBox(Prompt:= _

        "Please enter the stop row", Type:=1)

    ' Optional - remove all existing page breaks

    ActiveSheet.ResetAllPageBreaks

    ' Add new page breaks

    For lngRow = lngStart To lngStop Step lngStep

        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Rows(lngRow)

    Next lngRow

End Sub

Answer 3

Hans:  This work just the way I wanted.  Sorry I didn't respond sooner, but the weekend got in the way.  I do have another question:  Is there any way to modify this sub so that it will delete rows the same way that this sub inserts page breakes.  In other words, it will start with a given row, delete that row, then go down a given number of rows, and delete that row, etc., until it hits the stop row?

Answer 4

You can simply replace each Application.InputBox by a constant value, or even do away with the variables and place the values directly in the For ... statement:

Sub InsertPageBreaks()

    Dim lngRow As Long

    ' Optional - remove all existing page breaks

    ActiveSheet.ResetAllPageBreaks

    ' Add new page breaks

    For lngRow = 44 To 1221 Step 21

        ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Rows(lngRow)

    Next lngRow

End Sub

Answer 5

Hans:  This sounds like what I need.  I'll give it a try.  If it works, I'll want to modify it so that it will run as a stand-alone sub by changing the user input to actual numbers, but I think that I can figure that out.  Thanks for your help,