Delete rows if they contain specific text in Cells

[Livin404]

Greetings, I have some mixed results with these formulas. I can more easily complete this if it's on the active sheets, but since my macro will be launched from Worksheet ("72 Hr"), I need it to apply only to worksheet ("FIDS Inbound"). I don't think I need to identify any columns since the words identified below can only happen in certain ranges, so it would be the entire worksheet ("Inbound FIDS") it could apply to. The words would be "UPDATED BY:", "POST", and "FOREIGN". Currently I'm working with three macros, but I think it could be consolidated into one macro. The codes I have now are as follows: Thank you,

Sub Delete_Post()
    Dim rng As Range
    Set rng = Sheets("Inbound FIDS").UsedRange
    
    For I = rng.Cells.Count To 1 Step -1
        If rng.Item(I).Value = "*POST*" Then
            rng.Item(I).EntireRow.delete
        End If
    Next I
End Sub

Sub Delete_Foreign()
    Dim rng As Range
    Set rng = Sheets("Inbound FIDS").UsedRange
    
    For I = rng.Cells.Count To 1 Step -1
        If rng.Item(I).Value = "*Foreign*" Then
            rng.Item(I).EntireRow.delete
        End If
    Next I
End Sub
Sub Delete_Update()
    Dim rng As Range
    Set rng = Sheets("Inbound FIDS").UsedRange
    
    For I = rng.Cells.Count To 1 Step -1
        If rng.Item(I).Value = "UPDATED BY:" Then
            rng.Item(I).EntireRow.delete
        End If
    Next I

 

[Yongle]

For comparing text with wildcards, use Like

If rng.Item(I).Value Like "*POST*" Then

If rng.Item(I).Value Like "*Foreign*" Then

 

[Yongle]

don't think I need to identify any columns since the words identified below can only happen in certain ranges, so it would be the entire worksheet ("Inbound FIDS") it could apply to

You are correct that the VBA should work - BUT it will take longer to run if columns are not specified

 

[dmt32]

Hi,
try this update to your codes & see if it will do what you want

Sub Delete_Foreign_Post_Update()
    Dim rngDelete As Range, rng As Range
    Dim cell As Range
    
    Set rng = Worksheets("Inbound FIDS").UsedRange
    
    For Each cell In rng.Cells
         With cell
         If UCase(.Value) Like "*POST*" Or UCase(.Value) Like "*FOREIGN*" Or UCase(.Value) = "UPDATED BY:" Then
            If rngDelete Is Nothing Then Set rngDelete = cell Else Set rngDelete = Union(rngDelete, cell)
        End If
        End With
    Next cell
    If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

Dave

 

[LazyBug]

How about

Sub DelAll()
Dim c As Range, dc As Range
Dim arrSS(), i&
    Application.ScreenUpdating = False
 
    arrSS = Array("POST", "Foreign", "UPDATED BY:")
    For i = LBound(arrSS) To UBound(arrSS)
        For Each c In Sheets("Inbound FIDS").UsedRange.Rows
            If Not c.Find(arrSS(i), , xlValues, xlPart) Is Nothing Then
                If dc Is Nothing Then Set dc = c Else Set dc = Union(dc, c)
            End If
        Next
    Next i
    
    If Not dc Is Nothing Then dc.EntireRow.Delete
    
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

What about an even more "bulk" approach?

Sub Delete_Several()
  Dim vDeletes As Variant, itm As Variant
  
  vDeletes = Array("*post*", "*foreign*", "updated by:")
  Application.ScreenUpdating = False
  With Sheets("Inbound FIDS").UsedRange
    For Each itm In vDeletes
      .Replace What:=itm, Replacement:="#N/A", LookAt:=xlWhole, MatchCase:=False
    Next itm
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub