Insert row every X rows in excel

Question

I have a long list of codes such as 008.45, etc that will need 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

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

score 6 · Answer 1 · answered Feb 16 '11 at 14:10

6

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

answered Feb 16 '11 at 14:10

Fionnuala

90,370
7
114
152

that works perfect. thanks. i would, but i can't upboat you yet! – dzilla Feb 16 '11 at 14:14
@dzilla NP, you will get there :) – Fionnuala Feb 16 '11 at 14:17
Have one from me instead! +1 for the only complete answer. – Dave Feb 16 '11 at 14:19

score 1 · Answer 2 · answered Sep 28 '17 at 08:28

1

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

answered Sep 28 '17 at 08:28

score 1 · Accepted Answer · answered Feb 16 '11 at 13:47

1

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

answered Feb 16 '11 at 13:47

diagonalbatman

17,340
3
31
31

Thanks for the help, Would I enter this just in the cell? Or is there a specific area that I would need to enter it? – dzilla Feb 16 '11 at 13:50
It is VBA - so you would have to enter the code behind. I assumed as you had tagged this as a vba question you were familiar with this? To enter the code editor view ALT+F11 will get you there, you can then create functions / code etc. http://www.mvps.org/dmcritchie/excel/getstarted.htm this will help you with the basics. – diagonalbatman Feb 16 '11 at 13:51
1

If you insert 5 rows at row 5 with this, it seems to me that the next inserted rows (at row 10) will go just after the newly inserted, and so on. – Dr. belisarius Feb 16 '11 at 13:55
You would have to go back ways, or your count will keep changing. `For i=100 to 1 Step -1` – Fionnuala Feb 16 '11 at 13:56
Thats probably correct, in this case a decision has to be made on how the logic wants to be handled. Are we counting the new rows in our counter? In which case we need to increment i by the number of rows we just added. If the questioner provides more details on how he wants this to operate i can help with various solutions. – diagonalbatman Feb 16 '11 at 13:59
@remou your count wouldn't be changing - becuase i isn't based on the row number. If it were then you are correct. The questioner needs to decide how this should operate. – diagonalbatman Feb 16 '11 at 14:00
1

I cannot imagine any circumstance where the added rows would count. That could continue for a very, very long time. – Fionnuala Feb 16 '11 at 14:02
sorry for being nebulous i wanted 1,2,3,4,5 (break for five rows) 6,7,8,9,10 (break for five rows) 11,12,13,14,15 (break for five rows) until row 100 – dzilla Feb 16 '11 at 14:03
1

@ Andy Paton I have done this, and yes, your count would change, how else would you know where to insert the rows? `if i mod 5 = 0 Then InsertRowsAti` – Fionnuala Feb 16 '11 at 14:03

score 0 · Answer 4 · answered Jul 30 '12 at 21:29

0

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.

answered Jul 30 '12 at 21:29

mlunoe

4,422
4
32
43

score 0 · Answer 5 · edited Jul 20 '17 at 05:09

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

score 0 · Answer 6 · answered Feb 16 '11 at 14:06

0

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.

answered Feb 16 '11 at 14:06

Dave

3,438
20
13

score 0 · Answer 7 · answered Aug 20 '18 at 23:10

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

Insert row every X rows in excel

7 Answers7

Linked

Related