Macro to delete rows if cell contains stringClosed

Question

Hello, 

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.

/J

vcoolio · Answer

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"
           .Offset(1).EntireRow.Delete
           .AutoFilter
End With

Sheet1.Columns(9).ClearContents

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.

http://ge.tt/4J8NKio2

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.

Cheerio,
vcoolio.

Macro to delete rows if cell contains string

1 response

Similar discussions

Subscribe To Our Newsletter!