PDA

View Full Version : Insert alternative rows



sujittalukde
05-18-2007, 10:07 PM
Need a macro to insert alternative rows in a selected range.Someone please provide

Simon Lloyd
05-19-2007, 12:51 AM
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

sujittalukde
05-19-2007, 01:56 AM
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
05-19-2007, 01:59 AM
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



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

Simon Lloyd
05-19-2007, 02:14 AM
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:

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
not tested but shoud do the trick!

Simon Lloyd
05-19-2007, 02:16 AM
Just had a look at that, no it wont work!....back to the drawing board!

Simon Lloyd
05-19-2007, 02:31 AM
I've been to the drawing board and this is what i came up with!

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

Bob Phillips
05-19-2007, 02:57 AM
Why switch from finding the range as you did originally, to rqeuiring the range to be selected.

And, isn't



sa = Selection.Address
For Each MyCell In Range(sa)


the same as


For Each MyCell In Selection

Simon Lloyd
05-19-2007, 03:00 AM
Apologies Bob just pure ineptitude!

Bob Phillips
05-19-2007, 03:46 AM
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
05-19-2007, 03:56 AM
Tha last code provided by Simon is inserting only one row irrespective of rows selection

Simon Lloyd
05-19-2007, 03:57 AM
Worked fine for me with a contiguous selection

Simon Lloyd
05-19-2007, 04:12 AM
With prompting and proding from both Bob and Malcom!

Sub Macro1()
Dim i As Integer
For i = Selection.Rows.Count To 0 Step -1
Selection.Rows(i).EntireRow.Insert
Next
End Sub

sujittalukde
05-19-2007, 04:15 AM
But i want to insert one by one alternate rows to the entire selection and not just only one.

sujittalukde
05-19-2007, 04:17 AM
yeah the last code is working. thanks to all

mdmackillop
05-19-2007, 04:32 AM
A couple of points.
If you don't want the first line to move then use
For i = Selection.Rows.Count To 2 Step -1
If you don't want adjoining columns affected use
Selection.Rows(i).Insert