Insert alternative rows

[sujittalukde]

Need a macro to insert alternative rows in a selected range.Someone please provide

[Simon Lloyd]

[vba]Sub Insert_rows()
Dim Rng1 As Integer
Dim i As Integer
Rng1 = ActiveSheet.Range("A65536").End(xlUp).Rows
For i = 2 To Rng1 Step 2
ActiveSheet.Rows(i).Insert Shift:=xlDown
Next i
End Sub[/vba]

[sujittalukde]

The code is inserting rows in the range containing data. But the need is that the code should insert rows in the selected range. Suppose rows 1 to 100 contain data & if rows 25 to 38 is selected than the code should start inserting rows alternatively after every row starting from 26,28,& so on till 38 row is reached

[Bob Phillips]

problem wit that Simon is that rows at the end don't get processed. The reason is that if you start with 10 rows, you loop goes 2 to 10 step 2, but after the first 3 iterations your range is 13 rows long, so you miss thos last 3 rows, and any further rows shifted.

You need to go bottom up

[vba]

Sub Insert_rows()
Dim Rng1 As Integer
Dim i As Integer
Rng1 = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Rows
For i = Rng1 To 2 Step -1
ActiveSheet.Rows(i).Insert Shift:=xlDown
Next i
End Sub
[/vba]

[Simon Lloyd]

Yep!, i remember now Bob you keep telling me but i still keep constucting code in the top to bottom logic, i will try to remember in future.

Try something like:

[VBA]Dim i As Integer
Dim sc As Integer
sc = Selection.Rows.Count
For i = sc To 0 Step -1
Rows(i).Insert Shift:=xlDown
Next i
[/VBA]not tested but shoud do the trick!

[Simon Lloyd]

Just had a look at that, no it wont work!....back to the drawing board!

[Simon Lloyd]

I've been to the drawing board and this is what i came up with!
[vba]
Sub Macro1()
Dim MyCell As Range
Dim i As Integer
Dim sa As String
Dim sc As Integer
sc = Selection.Rows.Count
sa = Selection.Address
i = 1
For Each MyCell In Range(sa)
MyCell.Offset(i, 0).Insert Shift:=xlDown
i = i + 1
If i >= sc Then Exit Sub
Next
End Sub
[/vba]

[Bob Phillips]

Why switch from finding the range as you did originally, to rqeuiring the range to be selected.

And, isn't

[vba]

sa = Selection.Address
For Each MyCell In Range(sa)
[/vba]

the same as

[vba]
For Each MyCell In Selection
[/vba]

[Simon Lloyd]

Apologies Bob just pure ineptitude!

[Bob Phillips]

You were on the right track to start with, all you needed to do was go bottom to top rather than top to bottom. Everything else was good.

[sujittalukde]

Tha last code provided by Simon is inserting only one row irrespective of rows selection

[Simon Lloyd]

Worked fine for me with a contiguous selection

[Simon Lloyd]

With prompting and proding from both Bob and Malcom!
[VBA]
Sub Macro1()
Dim i As Integer
For i = Selection.Rows.Count To 0 Step -1
Selection.Rows(i).EntireRow.Insert
Next
End Sub

[/VBA]

[sujittalukde]

But i want to insert one by one alternate rows to the entire selection and not just only one.

[sujittalukde]

yeah the last code is working. thanks to all

[mdmackillop]

A couple of points.
If you don't want the first line to move then use
[vba] For i = Selection.Rows.Count To 2 Step -1
[/vba] If you don't want adjoining columns affected use
[vba] Selection.Rows(i).Insert
[/vba]