Jumping Around.Inserting Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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)




Rozb said:
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
Click to expand...
of code is run..it inserts two or sometimes four rows. Why is this happening?
 
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.
 
Back
Top