Delete row if

[Doedtman]

Hello!
I'm trying to delete all rows with "Yes" in column G, but for some reason it doesn't work. Would anyone have suggestions to help me fix this coding?

Dim rng As Range
Set rng = Sheets("Summary").Range("G3:G8000")
Dim c As Range
 For Each c In rng
    If c = "Yes" Then
        c.EntireRow.Delete
    End If
    Next c
End Sub

Thanks!

 

[BarryL]

you need to go backwards

Sub test()
Dim rng As Range
Set rng = Sheets("Summary").Range("G3:G8000")
Dim c As Long
 For c = 8000 To 3 Step -1
    If Cells(c, 7).Value = "Yes" Then
        Rows(c).EntireRow.Delete
    End If
    Next c
End Sub

 

[MattH1]

Is your range set as 3 to 8000?

Here's how I would approach it, though there may be a better way to do this:

Sub Doedtman()

Dim RowCount as Long
RowCount = Sheets("Summary").Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row

Sheets("Summary").Activate

For I = RowCount to 3 Step -1
If Cells(I,7) = "Yes" Then Cells(I,7).EntireRow.Delete
Next I

End Sub

Let me know if this works. I haven't tested it myself but it seems to be workable.

 

[Doedtman]

My range can actually change but always starts at 3 so the coding was perfect. Thank you!