Delete Rows That Do Not Contain a Certain Phrase

[Ottsel]

Struggling to make a for loop that'll delete a cell in column A if it doesn't contain the word "Lot" amongst the string of text.

Example:
G:\JOBCARDS\ADS\ERT\ADSERT78-85PHS11-PC5.xls
The following above would be deleted, since it doesn't contain the word "Lot" within it

G:\JOBCARDS\ADS\ERT\Lot2032.xls
This row would remain, since it does contain the word "Lot"

 

[RobP]

Hi Ottsel,

you could try this routine ? It will look for last row, then cycle backwards through the data, just clearing the contents of each cell that does not have "lot" inside it.

Sub delete_row()

Dim datalastrow, x As Long
Dim MyText As String

datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data

For x = datalastrow To 1 Step -1

    MyText = ActiveSheet.Range(Cells(x, 1), Cells(x, 1))
   
    If Not (InStr(1, MyText, "LOT", vbTextCompare)) Then
   
        'delete cell
        Range(Cells(x, 1), Cells(x, 1)).ClearContents
   
    End If

Next x

End Sub

cheers
Rob

 

[alansidman]

you could highlight the column of data and on the data tab, select filter. Click on the drop down and select "Text Filters" and then select "does not contain" and fill in "Lot"

 

[Ottsel]

Sub delete_row()

Dim datalastrow, x As Long
Dim MyText As String

datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data

For x = datalastrow To 1 Step -1

    MyText = ActiveSheet.Range(Cells(x, 1), Cells(x, 1))
  
    If Not (InStr(1, MyText, "LOT", vbTextCompare)) Then
  
        'delete cell
        Range(Cells(x, 1), Cells(x, 1)).ClearContents
  
    End If

Next x

End Sub

cheers
Rob

Worked wonders. Appreciate it Rob!

 

[Ottsel]

you could highlight the column of data and on the data tab, select filter. Click on the drop down and select "Text Filters" and then select "does not contain" and fill in "Lot"

Normally yes, but I need the rows removed for the export. I'm having this added to an already existing macro to help increase the overall process.
The system will pick up the hidden rows.

 

[johnnyL]

I'm confused, did you want the row removed or just the cell value? It makes a difference.

 

[johnnyL]

If the entire row should be removed ...

Perhaps something like:

Sub KeepRowsWhereColumnAEqualLot()
'
    With ActiveSheet
        .AutoFilterMode = False                                             '   Remove filter
        .UsedRange.AutoFilter Field:=1, Criteria1:="<>" & "*Lot*"           '   Look for cells in Column A that <> "*Lot*"
        .AutoFilter.Range.EntireRow.Delete                                  '   Delete the rows that 'passed inspection'
        .AutoFilterMode = False                                             '   Remove filter which leaves remaining rows
    End With
End Sub