VB script for inserting a blank row after specific row count

Question

Hello,

Hoping that somebody can provide me with some assistance.  I have to manually paste some information into a SAP module from Excel.  In the first pass, I will be entering 18 rows into SAP.  The subsequent runs will always have 17 rows inserted.  My data is about 3,000 rows deep.  Is there a way I can run a VB script that will insert a blank row after evaluating the first 18 rows and then insert a blank row after each subsequent 17 rows? 

Thank you for your help.

Answer 1

I've modified Shane's code to do what you asked and made a couple of other small modifications along the way. For example, I changed the code to use the empty column after the last column containing either data or formula so that Excel doesn't have to shift everything to the right to accomodate the new inserted column only to move everything to the left when it is deleted; and I also change the square-bracket notation to normal Range notation (sorry Shane, but I hate the square bracket notation). Here is the modified routine (if this answers your question, please mark both it AND Shane's responses as the Answer)...

Sub InsertRows()

    Dim Bot As Long, MTcol As Long

    Bot = Cells.Find(What:="*", SearchOrder:=xlRows, _

          SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

    MTcol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _

            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1

    With Range(Cells(1, MTcol), Cells(Bot, MTcol))

      .Formula = "=IF(ROW()=19,TRUE,IF(AND(ROW()>19,MOD(ROW()-19,17)=0),TRUE,1))"

      .SpecialCells(xlCellTypeFormulas, 4).EntireRow.Insert

      .EntireColumn.Delete

    End With

End Sub

Answer 2

Hi,

Assuming your data only goes down about 3000 rows:

Sub InsertRows()

    Dim bot As Long

    bot = [A5000].End(xlUp).Row

    [A1].EntireColumn.Insert

    Range("A1:A" & bot).Select

    Selection = "=IF(ROW()=18,TRUE,IF(AND(ROW()>18,MOD(ROW()-18,17)=0),TRUE,1))"

    Selection.SpecialCells(xlCellTypeFormulas, 4).EntireRow.Insert

    Selection.EntireColumn.Delete

End Sub

This is much faster than a loop.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 3

Hi Shane,

Thanks for the sharing the script with me.  One issue that I face is: 

It is inserting a blank row after every 17 rows.  What I was looking for is: a) Insert a blank row after the first 18th row and b) subsequently, insert a blank row after every 17 rows.

Hope this can be done.  Thanks.

Sam.