inserting rows

[paul_0722]

I have a spreadsheet with five lines of text:
line 1: TEXT
line 2: TEXT
line 3: TEXT
line 4: TEXT
line 5: TEXT

What I want to achieve with one macro:
line 1: TEXT
line 2: <Blank>
line 3: TEXT
line 4: <Blank>
line 5: TEXT
line 6: <Blank>
line 7: TEXT
line 8: <Blank>
line 9: <Blank>
line 10: TEXT

My code so far is not working - likely because I am missing something about ActiveCell property and activate method. I've tried to comment what I was thinking would happen but it's obviously not.
Any help appreciated.

[vba]
Option Explicit
Sub test()
''Active Cell is R1,C1
ActiveCell.Offset(1, 0).Activate ''from row 1, move down 1 row to row 2 and,
ActiveCell.EntireRow.Insert ''insert row
ActiveCell.Offset(1, 0).Activate ''from what is now row 3, move down 1 row to row 4 and,
ActiveCell.EntireRow.Insert ''insert row
ActiveCell.Offset(1, 0).Activate ''from what is now row 5, move down 1 row to row 6 and,
ActiveCell.EntireRow.Insert ''insert row
ActiveCell.Offset(1, 0).Activate ''from what is now row 7, move down 1 row to row 8 and,
ActiveCell.EntireRow.Insert ''insert row
ActiveCell.EntireRow.Insert ''insert another row
End Sub
[/vba]

[jproffer]

[VBA]Sub Insert()
For N = 2 To 1000
Range("A" & N).EntireRow.Insert
N = N + 1
Next
End Sub[/VBA]

You will have to run it thru TWICE as many rows as you have data. (Change the 1000 in line 1 to whatever you need)

[ilyaskazi]

Hi, try this code.......

[VBA]Sub TestInsertRowsInBetween()
Dim r As Long
Dim StartRow As Long

StartRow = 1 'Assign your starting row
With ActiveSheet
For r = 1 To 4
If r < 4 Then
.Cells(StartRow + 1, 1).EntireRow.Insert
Else
.Cells(StartRow + 1, 1).EntireRow.Insert
.Cells(StartRow + 1, 1).EntireRow.Insert
End If
StartRow = StartRow + 2
Next r
End With
End Sub[/VBA]

[mdmackillop]

The secret here is, when inserting (or deleting) rows, always start at the bottom. With Columns, start at the right.
[vba]Sub InsertRows()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Cells(i, 1).EntireRow.Insert
Next
End Sub

[/vba]
This also allows you easily to insert multiple rows eg
[vba]Cells(i, 1).Resize(3).EntireRow.Insert
[/vba]
For the specific layout as shown:
[vba]Sub InsertRows()
Dim i As Long
For i = 5 To 2 Step -1
If i = 5 Then
Cells(i, 1).Resize(2).EntireRow.Insert
Else
Cells(i, 1).EntireRow.Insert
End If
Next
End Sub

[/vba]

[paul_0722]

Thanks jproffer, ilyaskazi and mdmackillop for great responses - those are several approaches to the problem I had not considered & will try.

Ideally I would execute this code from any active cell in the spreadsheet using a keyboard shortcut. I think I can modify your ideas to that & will try to do so - but any further ideas on that modification are welcome.

Thanks again!

[naikadit]

hi mdmackillop i also have the same isue but what if i want to add a single row after testing a particular if condition

[ilyaskazi]

hi mdmackillop i also have the same isue but what if i want to add a single row after testing a particular if condition

You should use this line of code on passing your if condition...
[VBA]Cells(i, 1).EntireRow.Insert[/VBA]