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?
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.
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.
