Insert row every X rows in excel

I have a long list of codes such as 008.45, etc that will ne开发者_如何学Goed multiple lines of text to explain them. I have the list of codes and I would like to know how I can automatically insert a row every, say, fifth row. Example Below

1          
2
3
4
5
6
7
8
9
10...
100

Every five rows I would like to insert a given number of my choosing of rows. How can I do this? Thanks

Test with a range from row 1 to row 100.

Sub InsertRows()
For i = Sheet1.UsedRange.Rows.Count To 1 Step -5
    For j = 0 To 4
        Sheet1.Rows(i).Insert
    Next
Next
End Sub

You would need to use a loop as below:

for i=1 to 100 step 1
  if i mod 5 = 0 then
     // Insert the rows
  end if
next i

This worked great for me:

Sub add_rows_n()

t = 6
Do Until Cells(t, "A") = ""
Rows(t).Insert
t = t + 6
Loop

End Sub

To insert a row at row myRowNumber, your VBA code would look like this:

    Rows(myRowNumber).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

You can incorporate that into Andy's answer.

Or you could use the modulus function like so:

=IF(MOD(ROW()-1,7),"",A1)

in B1, where A1 is the first number of your dataset.

NB: Change 7 to n to get every n'th row.

For example if I want 5 of my records between my rows of data I would use Mod 6, however, you need to allow for these new rows as they will affect the used range count! To do this you will want to add the number of rows that will be inserted to the length of the loop (eg. Absolute value of(numberOfRows/YourModValue)).

Code to do this:

Sub InsertRows()
For i = 1 To Sheet1.UsedRange.Rows.Count + Abs(Sheet1.UsedRange.Rows.Count / 6) Step 1
    If i Mod 6 = 0 Then
        Sheet1.Rows(i).Insert
        Cells(i, 1).Value = "Whatever data you want in your new separator cell"
    End If
Next i
End Sub

Here's the code I wound up with. Note that the FOR loop actually runs backwards from the end of UsedRange. The Mod 5 inserts a row every 5 rows.

For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
    If (i - 1) Mod 5 = 0 Then
        ActiveSheet.Rows(i).Insert Shift:=xlDown
    End If
Next