Insert row every X rows in excel

Question

I have a long list of codes, such as 008.45, etc., which will require several lines of text to explain them. I have a 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 lines, I would like to insert a given number of my lines. How can i do this? Thanks

Answer 1

You will need to use a loop as shown below:

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

Answer 2

Test with a range from line 1 to line 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 

Answer 3

This worked fine for me:

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

Answer 4

To insert a string into myRowNumber , your VBA code would look like this:

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

You can include this in Andy's answer.

Answer 5

Or you can use the module function like this:

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

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

NB: change the value of 7 to n to get every nth line.

Answer 6

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

Code for 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