Macro to delete rows if cell contains string

jonny578 Posts 1 Registration date Friday February 23, 2018 Status Member Last seen February 23, 2018 - Feb 23, 2018 at 09:29 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Feb 24, 2018 at 07:50 AM

I need help creating an Excel macro. I have a large sheet that has cells with large amounts of text (error messages). I would like to delete all the rows that have known error messages in one of the cells of Column D.

Let's say that column D has a cells with "ABC DEF GHIJ", "dog ABC," "DEF GHIJ". I want to delete every row that has a cell containing the string "ABC".

Is there any way to do this? I know you can do it if the cell equals a certain value, but how about if the cell just contains certain text?

Thanks very much for your time.


1 response

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Updated on Feb 24, 2018 at 08:11 AM
Hello Jonny,

A relatively simple method to do the task for you would be to use a helper column to extract the string "ABC" from the entire string in any cell in Column D then filter the helper column for the extracted string "ABC" and then delete the relevant rows.

Following is the code that should do the task:-

Sub DeleteIt()

Dim lr As Long

Application.ScreenUpdating = False

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Range("I2:I" & lr) = "=IF(D2="""","""",IF(ISNUMBER(SEARCH(""ABC"",D2,1)),""ABC""))"

With Sheet1.[A1].CurrentRegion
           .AutoFilter 9, "ABC"
End With


Application.ScreenUpdating = True

End Sub

The code firstly inserts a formula (line 8 in the code above) in Column I (the helper column in this case) which extracts "ABC" from the string in Column D.
The code then filters for "ABC" in Column I and deletes the relevant rows of data. The helper column is then cleared.

As I don't know the set out of your worksheet, following is the link to a sample that I have prepared for you so you can see how it works.

The sample has a few rows of data with only three that don't contain "ABC" in Column D so only three rows of data should be left once filtering/deleting is completed. Click on the "RUN" button to see it work.

I hope that this helps.