PDA

View Full Version : inserting rows



paul_0722
08-24-2008, 07:36 PM
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.


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

jproffer
08-24-2008, 08:00 PM
Sub Insert()
For N = 2 To 1000
Range("A" & N).EntireRow.Insert
N = N + 1
Next
End Sub

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
08-25-2008, 12:13 AM
Hi, try this code.......

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

mdmackillop
08-25-2008, 12:28 AM
The secret here is, when inserting (or deleting) rows, always start at the bottom. With Columns, start at the right.
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


This also allows you easily to insert multiple rows eg
Cells(i, 1).Resize(3).EntireRow.Insert

For the specific layout as shown:
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

paul_0722
08-25-2008, 01:59 PM
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
08-25-2008, 04:04 PM
hi mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) i also have the same isue but what if i want to add a single row after testing a particular if condition

ilyaskazi
08-25-2008, 11:06 PM
hi mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) 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...
Cells(i, 1).EntireRow.Insert