VBA to insert two rows, skip one, insert two

[marrick13]

I am trying to write code to insert two empty rows after a data line, then skip one data line, then insert two empty rows, then skip, etc. This is for a 3-column table that can vary in length. The purpose is to allow the data to be pasted into an Excel file that is both password protected and which contain merged cells (so I cannot unprotect it or un-merge any cells). I have been modifying variations of the following, which inserts one row, not two:

Sub Insert()
Dim i As Long, s, e
s = InputBox("Starting Row")
e = InputBox("Ending Row" & Chr(10) & Chr(10) & _
"(Must be equal or greater than Starting Row)")
Application.ScreenUpdating = False
If (s = "") + (e = "") + (s > e) Then Exit Sub
If (IsNumeric(s) = False) + (IsNumeric(e) = False) Then Exit Sub
For i = s To e * 2 - s Step 2
Rows(i).insert
Next
Application.ScreenUpdating = True

End Sub

This code has a nice feature in that it prompts for the entry of a starting and ending row, and requires that one only place the cursor in the starting row, but as it inserts only one row, it won't serve the purpose. Variations have not produced the desired result. I don't know Excel VBA well enough to figure it out on my own...

 

[jbeaucaire]

Try this:

Sub Insert()
Dim i As Long, s As Long, e As Long

s = Application.InputBox("Enter starting row", "First Row", 2, Type:=1)
If s = 0 Then Exit Sub

e = Application.InputBox("Enter ending row", "Last Row", 2, Type:=1)
If e = 0 Or e < s Then Exit Sub

    For i = e To s Step -2
        Rows(i + 1).Resize(2).Insert
    Next i

Application.ScreenUpdating = True
End Sub

 

[marrick13]

Thank you. It's better, and it does insert two rows, but after each pair of values (part #s) instead after each one. This is what some of the file looks like after running your code:

Row
2 RC628054E
3 RC628054F
4
5
6 RC628054G
7 RC628054I
8
9
10 RC628752G
11 RC628054M


The way it should look is as follows:

Row
1 (Header)
2 RC628054E
3
4
5 RC628054F
6
7
8 RC628054G
9
10
11 RC628054I
12
13
14 RC628752G
15
16

etc.

 

[jbeaucaire]

Change it so Step -1 then.

 

[marrick13]

That's it! Perfect! Thanks so much.

 

[huibster]

Yes, thanks jbeaucaire for this solution and marrick13 for posting the question to begin with. Many more people benefit from threads than those who post the questions or solve them

So again, thanks