VBA to Remove all rows in Table that contains a speified text string

[Kpersen]

I have search the forum but found no thread that was able to solve my issue:

I have a spreadsheet named "Files" in which I have a table named "data" where header begins in row 2.
My header in column A2 is "filenumber" and in that column I need to delete entire row where the text "voc" appears as part of the value.

The "voc" value is not just always "voc" only but followed by numbers and other text.
I just need it to delete the entire row if the cell in column A (filenumber) contains "voc"

I tried a macro that would filter by values that contained VOC but there were to many records to make this work with an autofilter.

Any good suggestions?

 

[Rick Rothstein]

This may not work because of the huge number of implied rows in your Table, but give it a try anyway and let us know how it did...

Sub DeleteRowsWithVoc()
  With Sheets("Files").ListObjects("Data").DataBodyRange
    .Columns(1).Replace "*voc*", "#N/A", xlWhole, , False, , False, False
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, .Columns).Delete
  End With
End Sub

 

[Kpersen]

Hi Rick

This is a good suggestion but the macro does not solely look for errors in column A when deleting the row.
That causes an issue when if a non-VoC row contains and error in another column as those would be deleted as well.

Is there a way to delete the rows based solely on error values in column A?

 

[Rick Rothstein]

Hi Rick

This is a good suggestion but the macro does not solely look for errors in column A when deleting the row.
That causes an issue when if a non-VoC row contains and error in another column as those would be deleted as well.

Is there a way to delete the rows based solely on error values in column A?

Untested because I am about to leave the house right now, but give this a try...

Sub DeleteRowsWithVoc()
  With Sheets("Files").ListObjects("Data").DataBodyRange
    .Columns(1).Replace "*voc*", "#N/A", xlWhole, , False, , False, False
    Intersect([B][COLOR="#FF0000"].Columns(1)[/COLOR][/B].SpecialCells(xlConstants, xlErrors).EntireRow, .Columns).Delete
  End With
End Sub

 

[Kpersen]

This worked. Thank you Rick