Jumping Around.Inserting Rows

[Guest]

Hi All

I have a little code listed below for which a blank row is inserted after each row containing "Group". When the little piece of code is run..it inserts two or sometimes four rows. Why is this happening

Thanks
Rozb

Sub FindGroupRow(
ActiveSheet.Range("A2:A1000").Selec
Rng = Selection.Rows.Coun
ActiveCell.Offset(0, 0).Selec
Application.ScreenUpdating = Fals
For i = 1 To Rn
If ActiveCell.Value <> " " The
Cells.Find(What:="group", after:=ActiveCell, LookIn:=xlValues, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
False).Offset(1, 0).Activat
Selection.EntireRow.Insert Shift:=xlDow
Els
ActiveCell.Offset(1, 0).Selec
End I
Next
Application.ScreenUpdating = Tru
End Sub

 

[Frank Kabel]

Hi
you have to count starting in the last row. So change your line
For i = 1 To Rng

to
For i = Rng to 1 step -1

 

[Ron de Bruin]

Hi Rozb

Try this one for whole column A

Sub test()
Dim Rng As Range
FindString = "group"
Set Rng = Range("A:A").Find(What:=FindString, LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.Offset(1, 0).EntireRow.Insert
Set Rng = Range("A" & Rng.Row + 1 & ":A" & Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Hi All,

I have a little code listed below for which a blank row is inserted after each row containing "Group". When the little piece

of code is run..it inserts two or sometimes four rows. Why is this happening?

 

[Guest]

Ron..Thank you. Rozb

 

[Guest]

Thank you Frank.

 

[Tom Ogilvy]

If you are going to loop, you might as well skip the Find command (looping
backwards alone won't fix your problem).

Sub FindGroupRow()
ActiveSheet.Range("A2:A1000").Select
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1000 to 2 step -1
If Instr(1,ActiveCell.Value,"Group",vbTextCompare) Then
ActiveCell.Offset(1,0).EntireRow.Insert Shift:=xlDown
End if
ActiveCell.Offset(1, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

This assumes "group" is in column A.

 

[Guest]

Thank you Tom.