2

Possible Duplicate:
Insert row every X rows in excel

I have a large set of data (let's say it goes from B5 to J500 and let's say this range is named rngOutput). I am trying to go through this data and add 2 empty rows every x number of rows where x is a number the user specifies. For example if x is 10 then every 10 rows 2 new rows should be inserted. Conceptually, this is the code that should work:

For i = 1 to Number of rows in rngOutput
   If i mod x = 0 Then
        Insert 2 Rows
   End If
Next i

However, when you insert 2 new rows, the row count changes and the formula messes up (i.e. it adds 2 rows after the first 10 rows, then it adds another 2 rows after the next 8 rows (since it counts those 2 new rows you added as actual rows) then it adds another 2 rows after the next 6 rows, etc.

I am trying to figure out a way to accomplish adding 2 new rows every x number of rows cleanly to avoid the above problem.

Thank you for the help and please let me know if you need additional clarification!

Community
  • 1
  • 1
Max_1234
  • 119
  • 2
  • 2
  • 7
  • You can avoid loops - which can be lengthy - by using either an `AutoFilter` on a formula testing the `Mod` of each row in the range of interest, or by deriving the row numbers for a range insert with an approach such as http://stackoverflow.com/questions/12921350/is-it-possible-to-fill-an-array-with-row-numbers-which-match-a-certain-criteria/13061123#13061123 – brettdj Dec 29 '12 at 23:49

2 Answers2

5

This is like Chris's only fleshed out. When inserting or deleting rows you have to work up from the bottom:

Sub InsertXRowsEveryYRows_WithMeaningfulVariableNames()
Dim NumRowsToInsert As Long
Dim RowIncrement As Long
Dim ws As Excel.Worksheet
Dim LastRow As Long
Dim LastEvenlyDivisibleRow
Dim i As Long

NumRowsToInsert = 2     'any number greater than 0
RowIncrement = 10       'ditto
Set ws = ActiveSheet
With ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    LastEvenlyDivisibleRow = Int(LastRow / RowIncrement) * RowIncrement
    If LastEvenlyDivisibleRow = 0 Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = LastEvenlyDivisibleRow To 1 Step -RowIncrement
        .Range(i & ":" & i + (NumRowsToInsert - 1)).Insert xlShiftDown
    Next i
End With
Application.ScreenUpdating = True
End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • 1
    Actually I guess it's a little different than Chris's and your original, as it doesn't use Mod. – Doug Glancy Dec 29 '12 at 23:22
  • +1 for more efficient code turning off `ScreenUpdating` – brettdj Dec 29 '12 at 23:50
  • @brettdj, thanks! I realized though that I didn't understand the part about only inserting every x rows. While adding that, I figured out that the code should just determine the last evenly divisible row and step backwards by the specified row increment, which could make a lot less looping. I think I'm done now :). – Doug Glancy Dec 30 '12 at 03:23
4

Count from the bottom of the range

For i = Number of rows in rngOutput to 1 step -1
   If i mod x = 0 Then
        Insert 2 Rows
   End If
Next i
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Let's say there are 500 entries and a use specified every 6 rows then it will be the first 2 rows will have two rows after them when the top 6 rows should have 2 rows after them. – Max_1234 Dec 29 '12 at 23:08
  • @Max_1234 Try it, it works because it steps backwards through your range so inserted rows do not throw off the calculations because they are placed below the rows being inspected. This is the correct answer. – Daniel Dec 29 '12 at 23:10
  • Won't it only work if the number of rows is exactly divisible by the number the user specifies? If there is a remainder, then it will skip the correct number of rows but the top rows will not have the correct spacing since I need 2 rows from the top. – Max_1234 Dec 29 '12 at 23:13
  • 1
    @max no, it won't. Try it! – chris neilsen Dec 29 '12 at 23:14
  • great, thank you! appreciate the help – Max_1234 Dec 29 '12 at 23:15