Delete rows if they contain specific text in Cells

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,

VBA Code:
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
For comparing text with wildcards, use Like
Rich (BB code):
If rng.Item(I).Value Like "*POST*" Then

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,477
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update to your codes & see if it will do what you want

VBA Code:
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
 
Solution

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
158
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,370
Office Version
  1. 365
Platform
  1. Windows
What about an even more "bulk" approach?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,776
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top