For Each .... Next - spasmodic results

[SharonJ]

This is a very simple procedure, yet for some rows of the spreadsheet the code works, and for others it doesn't. If I stop the procedure at that point and go back to the row that didn't work initially and re-run it, it normally works on that row, but then doesn't work on a row further down. Can I do something to make this work 100%? Thanks Sharon

Dim r as Range

Worksheets("Plan 1").Range("Q7").Activate

For Each r in Range ("Q7:Q350")
If r.text = "empty" then
Selection.EntireRow.Delete
Else
Selection.Offset(1,0).Select
End If
Next

 

[Norie]

Sharon

When deleting you need to loop backwards, you can't do that with a For...Next loop.

Try this.

Dim I As Long
 
For I = 350 To 7 Step -1
 
      Set r = Range("Q & I)
 
      If r.Value = "empty" Then
          r.EntireRow.Delete
      End if
 
Next I

 

[SharonJ]

Thanks Norrie.

But now I'm getting the Runtime Error 1004 Method Range of Object '_Global' failed.

 

[shadow12345]

Sub test()
Dim I As Long
 
For I = 350 To 7 Step -1
 
      Set r = Range("Q" & I)
 
      If r.Value = "empty" Then
          r.EntireRow.Delete
      End If
 
Next I

End Sub

Maybe... Norrie's stuff is way to complex for me, but that might be it. (Just added a " after the Q)

 

[Peter_SSs]

Sharon

Welcome to the MrExcel board!

Another possible way without looping at all ..

Sub delete_rows()
    Application.ScreenUpdating = False
    With Worksheets("Plan 1").Range("Q6:Q350")
        .AutoFilter Field:=1, Criteria1:="=Empty"
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[SharonJ]

Sorry - my fault. I typed Range("Q & I")

Thank you so much for your help.

 

[SharonJ]

Thanks Peter for your assistance too.

 

[Norie]

Nope, it's my fault - I missed the closing quote for Q.

 

[shadow12345]

Holy cow.... I got one right!

*dusts off cap*
*Insert feather*
*Struts around office*

Nice to know that eventually some of this stuff is starting to sink in a bit.