VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks

 

[taurean]

Try:

Sub Test_Macro()
Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To Last
If InStr((Cells(i, "B").Value), "ltd") > 0 _
Or InStr((Cells(i, "B").Value), "limited") > 0 _
Or InStr((Cells(i, "B").Value), "plc") > 0 Then
Cells(i, "J").Value = "Y"
Else: Cells(i, "J").Value = "N"
End If
Next i
End Sub

Edit: Formula will work fine. Are you interested?

 

[simopie]

Thanks for your help,

I actually figured out that with the code I was playing around in, it was case sensitive and therefore not returning any correct results.

I ended up running a basic Like "*string*" in an If Function

Regards

 

[sanketlohar]

How to use it with entire sheet selection ?

 

[Maisey17]

j

 

[Will BK]

Hi there,

To go back to earlier thread from (Jun 2002-Marc F), I'm trying to run that Macro but I would like to assign this to a specific tab from my worksheet. Tab is called "Data" and it's the second out of three tabs if this helps.
In addition, could I run the above and add it to a "recorded macro" - could you help with the wording to put two macros together?

Many thanks for your help.

 

[ahansche]

What if I am trying to delete rows that contain multiple different values of text? I am trying to get rid of rows that contain "BL18", "AN06", "MP01", etc.

I tried varying the filter like this:

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("d1", Range("d" & Rows.Count).End(xlUp))
.AutoFilter 1, "*AP01*"
.AutoFilter 1, "*AN02*"
.AutoFilter 1, "*CA07*"
.AutoFilter 1, "*CC05*"
.AutoFilter 1, "*FS50*"
.AutoFilter 1, "*HZ*"
.AutoFilter 1, "*AL03*"
.AutoFilter 1, "*AN06*"
.AutoFilter 1, "*CA08*"
.AutoFilter 1, "*CC14*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

It seems that it can only handle one value at a time, how can I modify to include multiple values?

thanks!

 

[ahansche]

what if there is more than one value I want to look up and delete rows for?

 

[MARK858]

Try the code below. To add more criteria amend the part in red.

Sub DelIt()
    Dim rFnd As Range, dRng As Range, rFst As String, myList, ArrCnt As Long
    myList = Array("BL18", "AN06", "MP01")

    For ArrCnt = LBound(myList) To UBound(myList)
        With Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
            Set rFnd = .Find(what:=myList(ArrCnt), _
                             LookIn:=xlValues, _
                             lookat:=xlPart, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext, _
                             MatchCase:=True)

            If Not rFnd Is Nothing Then
                
                rFst = rFnd.Address
                Do
                    If dRng Is Nothing Then
                        Set dRng = Range("A" & rFnd.Row)
                    Else
                        Set dRng = Union(dRng, Range("A" & rFnd.Row))
                    End If
                    
                    Set rFnd = .FindNext(After:=rFnd)
                
                Loop Until rFnd.Address = rFst
            End If
            
            Set rFnd = Nothing
        End With
    Next ArrCnt
    
    If Not dRng Is Nothing Then dRng.EntireRow.Delete

End Sub

 

[ahansche]

Thanks mark! That works perfectly!

Try the code below. To add more criteria amend the part in red.

Sub DelIt()
    Dim rFnd As Range, dRng As Range, rFst As String, myList, ArrCnt As Long
    myList = Array("BL18", "AN06", "MP01")

    For ArrCnt = LBound(myList) To UBound(myList)
        With Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
            Set rFnd = .Find(what:=myList(ArrCnt), _
                             LookIn:=xlValues, _
                             lookat:=xlPart, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext, _
                             MatchCase:=True)

            If Not rFnd Is Nothing Then
                
                rFst = rFnd.Address
                Do
                    If dRng Is Nothing Then
                        Set dRng = Range("A" & rFnd.Row)
                    Else
                        Set dRng = Union(dRng, Range("A" & rFnd.Row))
                    End If
                    
                    Set rFnd = .FindNext(After:=rFnd)
                
                Loop Until rFnd.Address = rFst
            End If
            
            Set rFnd = Nothing
        End With
    Next ArrCnt
    
    If Not dRng Is Nothing Then dRng.EntireRow.Delete

End Sub

 

[RichJW]

Hi, this is such a wonderful thread, whereby I have already used something from it, but could do with some help with the below.

As shown in page 1 of this thread, I wanted to remove all rows that contained any data in cells in column T, so only leaving blanks - BUT I wanted it to start from row 7 as I have blank rows above this that need to be kept.

I tried the below...but it deletes my title rows. It must be simple, but how do I get this to start from row 7?

Last = Cells(Rows.Count, "T").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "T").Value) <> "" Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub

Thanks,
Richard