VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks

 

[SiuGuy007]

When I run the code shown below, the spreadsheet does not change at all.

Sub DeleteRowWithAliveWellCheck()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Alive Well" IN COLUMN W
'========================================================================
Last = Cells(Rows.Count, "W").End(xlUp).Row
For AC = Last To 1 Step -1
If (Cells(AC, "D").Value) = "Alive Well" Then
'Cells(AC, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(AC, "A").EntireRow.Delete
End If
Next AC
End Sub

 

[V_Malkoti]

Your VBA comment says that "Alive Well" is text in Column W, but in your code, you are checking for that text in column D - Cells(AC, "D").Value.

 

[MARK858]

Cells(AC, [COLOR="#FF0000"]"D"[/COLOR])

this is referencing column D not column W.

Cells(AC, [COLOR="#FF0000"]"A"[/COLOR])

this is referencing column A not column W.

For AC = Last To [COLOR="#FF0000"]1[/COLOR] Step -1

this ends the code in row 1 not row 2

 

[SiuGuy007]

Thank you...Is this correct? I am not sure about For AC = Last To 1 Step -1

Sub DeleteRowWithAliveWellCheck()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Alive Well" IN COLUMN W
'========================================================================
Last = Cells(Rows.Count, "W").End(xlUp).Row
For AC = Last To 1 Step -1
If (Cells(AC, "W").Value) = "Alive Well" Then
'Cells(AC, "W").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(AC, "W").EntireRow.Delete
End If
Next AC
End Sub

 

[MARK858]

Needs to be a 2 not a 1 and the code you might find is too slow for your needs (it will get there though).

Sub DeleteRowWithAliveWellCheck()
    '========================================================================
    ' DELETES ALL ROWS FROM [COLOR="#FF0000"]A2[/COLOR] DOWNWARDS WITH THE WORDs "Alive Well" IN COLUMN W
    '========================================================================
    Application.ScreenUpdating = False
    Last = Cells(Rows.Count, "W").End(xlUp).Row
    For AC = Last To [COLOR="#FF0000"]2[/COLOR] Step -1
        If (Cells(AC, "W").Value) = "Alive Well" Then
            'Cells(AC, "W").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(AC, "W").EntireRow.Delete
        End If
    Next AC
    Application.ScreenUpdating = True
End Sub

 

[SiuGuy007]

Thanks again...I am a VBA newb, so please forgive me. I copied the Code and run it. Again, not changes to the spreadsheet. In column W, the text will be Alive/Well Check or Alive/Well Check, Activity Check.
When the code looks for the word does it have to be an exact match or if it find the 2 words, even if there are additional words, coma, slash etc?

 

[V_Malkoti]

If you are looking for a partial match, then use Like operator instead of equility (=).

So instead of:

If (Cells(AC, "W").Value) = "Alive Well" Then

writing this:

If (Cells(AC, "W").Value Like "*Alive*") Or (Cells(AC, "W").Value Like "*Well*") Then

will mean that the code inside If should run if either the word Alive or Well was found in the cell.

 

[SiuGuy007]

Thanks........For your help

 

[paulxmw]

how about using auto filter then delete row

ActiveSheet.Range("$A$1:$Y$15000").AutoFilter Field:=1, Criteria1:=Array( _
"your choice at word or frase"), Operator:= _
xlFilterValues
Range("A2:I15000").SpecialCells(xlCellTypeVisible).EntireRow.Delete 'This delets the range

 

[SiuGuy007]

Much appreciated.....