Delete rows which contain certain text in a cell

[AndyM90]

Hi All,

I have some data which I need to sort through and as part of my macro, I want to delete rows which contain certain text. So for example, in column G if the word "Late" appears in cell G50, I want to delete row 50. From other threads I have found the below code which works, but I now want to expand this to include another column, do you know how I can do this?

End point:

Column G will have no cells in it which contain the word "Late"
Column M will have no cells in it which contain the word "Cash"

Thanks!

Andy

Dim c As Range
Dim SrchRng

Set SrchRng = ActiveSheet.Range("G2:G1000")
Do
Set c = SrchRng.Find("Late", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

 

[Joe4]

One way is to simply add another loop that does the exact same process on column M:

[COLOR=#333333]Set SrchRng = ActiveSheet.Range("G2:G1000")[/COLOR]
[COLOR=#333333]Do[/COLOR]
[COLOR=#333333]    Set c = SrchRng.Find("Late", LookIn:=xlValues)[/COLOR]
[COLOR=#333333]    If Not c Is Nothing Then c.EntireRow.Delete[/COLOR]
[COLOR=#333333]Loop While Not c Is Nothing

[/COLOR][COLOR=#333333]Set SrchRng = ActiveSheet.Range("M2:M1000")[/COLOR]
[COLOR=#333333]Do[/COLOR]
[COLOR=#333333]    Set c = SrchRng.Find("Cash", LookIn:=xlValues)[/COLOR]
[COLOR=#333333]    If Not c Is Nothing Then c.EntireRow.Delete[/COLOR]
[COLOR=#333333]Loop While Not c Is Nothing[/COLOR]

 

[AndyM90]

Thanks Joe. My macro seems to be skipping this section of code now, do you know why this may be? The last action that works is sorting the data a to z, then I want to delete those rows, then continue to another section (which works).

Workbooks.Open Filename:="C:XXXXXXX"
Workbooks.Open Filename:="C:XXXXXXXX"

Application.CutCopyMode = False
Application.ScreenUpdating = False
Windows("ABC.xls").Activate
Columns("L:U").Delete
Range("A1:U500").Select
Selection.Copy
Windows("ABC.xlsx").Activate
Sheets("ABC").Range("A1").PasteSpecial xlPasteValues
Cells.EntireColumn.AutoFit
Range("A2:U380").Select
Selection.sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom



Dim c As Range
Set SrchRng = ActiveSheet.Range("G2:G1000")
Do
Set c = SrchRng.Find("L", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing


Set SrchRng = ActiveSheet.Range("D2:D1000")
Do
Set c = SrchRng.Find("Cash", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

 

[JackDanIce]

Have you tried to filter for the values you want to delete, then use .SpecialCells(xlCellTypeVisible) to delete the visible rows? It's usually quicker to do it this way than by looping.

 

[AndyM90]

Sorry Jack, I'm a bit of a novice, can you explain .SpecialCells part in a little more detail?

My problem is that we have data which needs to be reconciled daily, and i'm trying to remove as much manual points as possible to reduce errors.

 

[Joe4]

I cannot see any reason why that would not run.
Make sure that you are on the correct file & sheet when that file runs.
It looks like you are on file ABC, on sheet ABC when it runs.

 

[JackDanIce]

SpecialCells: SpecialCells Method. Select Specific Cell Types Excel VBA