Delete ntire row if cell = Yes

[Shellys28]

I want to delete any row in the "Delivered" sheet that has "Yes" in a cell in Column "G:G"

For Each cell In Sheets("Delivered").Range("G:G")
If cell.Value = "Yes" Then
deleteRow = cell.Row
End If

 

[My Aswer Is This]

Try this:

Sub Filter_Me()
'Modified 12-18-17 9:50 PM EST
Application.ScreenUpdating = False
    With Sheets("Delivered").Range("G1:G" & Sheets("Delivered").Cells(Rows.Count, "G").End(xlUp).Row)
        .AutoFilter 1, "Yes"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[hiker95]

Try this:

Sub Shellys28()
' hiker95, 12/19/2017, ME1035831
Dim Addr As String
Addr = "G1:G" & Cells(Rows.Count, "G").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@="""","""",IF(@=""Yes"",""#N/A"",@))", "@", Addr))
On Error GoTo NoDeletes
Columns("G").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End Sub

 

[hiker95]

The above macro, in my reply #3 will run in the Active Worksheet.


Try this:

Sub Shellys28_V2()
' hiker95, 12/19/2017, ME1035831
Dim Addr As String
With Sheets("Delivered")
  Addr = "G1:G" & .Cells(.Rows.Count, "G").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@="""","""",IF(@=""Yes"",""#N/A"",@))", "@", Addr))
  On Error GoTo NoDeletes
  .Columns("G").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End With
End Sub