Insert rows at every nth row

[Xrull]

Hello,
I posted this in the wrong forum (General Excel Discussion & Other Questions)
I need to insert a row every 19th row after A2
I tried using this code:

Sub addblankrowevery19()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 20 Step -19
Rows(i).Insert
Next i
End Sub

But it didn't work.
Can someone guide me on how to get it to work, or a better solution?
Thanks,
Xrull

 

[GTO]

Try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> addblankrowevery19()<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "a").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 20 <SPAN style="color:#00007F">Step</SPAN> -19<br>        Rows(i & ":" & i).Insert xlShiftDown<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Xrull]

GTO,
I tried your code, but it stopped at row 38.
Thanks,
Xrull

 

[Smitty]

Perhaps:

Sub addblankrowevery19()
    Dim i As Long
    
    For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -19
        Rows(i).Insert
    Next i
    
End Sub

Although I do see problems with it in things evening out (unless you have rows that are a multiple of 19).

HTH,

Note: I deleted the errant post.

 

[Xrull]

Smitty,
It worked, but is it possible to insert from the top down, instead of from the bottom up?
Xrull

 

[GTO]

This seems to work:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> addblankrowevery19()<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 19 <SPAN style="color:#00007F">To</SPAN> Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">Step</SPAN> 19<br>        lCnt = lCnt + 1<br>        Rows(i + lCnt & ":" & i + lCnt).Insert xlShiftDown<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[GTO]

Oops. Add in screenupdating to speed it up.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> addblankrowevery19()<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 19 <SPAN style="color:#00007F">To</SPAN> Cells(Rows.Count, "A").End(xlUp).Row <SPAN style="color:#00007F">Step</SPAN> 19<br>        lCnt = lCnt + 1<br>        Rows(i + lCnt & ":" & i + lCnt).Insert xlShiftDown<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>