VBA delete row if contains specific text in column

[ahansche]

Hi,

I am trying to run a macro to delete an entire row If the value in column D contains a certain text. I am using the autofilter method. Here is my code:

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("d1", Range("d" & Rows.Count).End(xlUp))
.AutoFilter 1, "*AL03*"
.AutoFilter 1, "*AL23*"
.AutoFilter 1, "*AN06*"
.AutoFilter 1, "*CA08*"
.AutoFilter 1, "*CA10*"
.AutoFilter 1, "*CA12*"
.AutoFilter 1, "*CC12*"
.AutoFilter 1, "*CC14*"
.AutoFilter 1, "*FA40*"
.AutoFilter 1, "*HZ36*"
.AutoFilter 1, "*HZ37*"
.AutoFilter 1, "*HZ38*"
.AutoFilter 1, "*HZ40*"
.AutoFilter 1, "*HZ41*"
.AutoFilter 1, "*HZ47*"
.AutoFilter 1, "*HZ49*"
.AutoFilter 1, "*HZ56*"
.AutoFilter 1, "*HZ59*"
.AutoFilter 1, "*MP01*"
.AutoFilter 1, "*SI*"
.AutoFilter 1, "*ST86*"
.AutoFilter 1, "*ANO2*"
.AutoFilter 1, "*AP01*"
.AutoFilter 1, "*CA07*"
.AutoFilter 1, "*CA09*"
.AutoFilter 1, "*CC05*"
.AutoFilter 1, "*CC17*"
.AutoFilter 1, "*FS50*"
.AutoFilter 1, "*GL10*"
.AutoFilter 1, "*HP*"
.AutoFilter 1, "*HZ34*"
.AutoFilter 1, "*HZ35*"
.AutoFilter 1, "*HZ37*"
.AutoFilter 1, "*HZ39*"
.AutoFilter 1, "*HZ40*"
.AutoFilter 1, "*HZ41*"
.AutoFilter 1, "*HZ46*"
.AutoFilter 1, "*HZ48*"
.AutoFilter 1, "*HZ50*"
.AutoFilter 1, "*HZ52*"
.AutoFilter 1, "*HZ53*"
.AutoFilter 1, "*HZ61*"
.AutoFilter 1, "*SAHZ*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

What am I doing wrong?

Thanks!

Alex

 

[onlyadrafter]

Hello,

you need to filter and delete one criteria at a time

With Range("d1", Range("d" & Rows.Count).End(xlUp))
.AutoFilter 1, "*AL03*"
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter 1, "*AL23*"
.Offset(1).SpecialCells(12).EntireRow.Delete

and so on.

don't think you need to on error code. Would you ever have more than one instance of the text?