For Barrie Davidson

Posted by Paul B on October 03, 2001 9:16 AM

Barrie the code that Qroozn posted was not what I was looking for, you posted this Marco awhile back. It added a row after text that was the same, if column A had test in row 1,2,3 and Price in rows 4,5,6 it would insert a row after row 3.Would you please modify it to show a input box so the number of rows to be inserted could be changed. Thanks

Sub Insert_row()
' Written by Barrie Davidson
Dim Number_of_rows As Long
Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("A2").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value &LT;&GT; Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 1
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
End Sub

Posted by Juan Pablo on October 03, 2001 9:29 AM

How about 197.html

Posted by Paul B on October 03, 2001 9:35 AM

Not what I need, this inserts a row after "each" row

Posted by Juan Pablo on October 03, 2001 9:42 AM

Ok, ok, then try this one

Sub Insert_row()
' Written by Barrie Davidson
Dim Number_of_rows As Long
Dim Rowinsert As Integer
Number_of_rows = Range("A65536").End(xlUp).Row
Ans = InputBox("How many rows do you want to insert ?", "Rows", 1)
If IsNumeric(Ans) Then
Rowinsert = CInt(Ans)
Else
MsgBox "Invalid number entered", vbCritical, "Error"
Exit Sub
End If
Range("A2").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value &LT;&GT; Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Resize(Rowinsert).Insert
Number_of_rows = Number_of_rows + Rowinsert
Selection.Offset(Rowinsert + 1, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
End Sub

Juan Pablo

Posted by Barrie Davidson on October 03, 2001 10:01 AM

Thanks Juan. Boy, everybody's quick today!! (nt)