Deletes rows that do not have a specific word in a specific column

[FireRulerX]

Here is the code I am using but can not make it viable to a specifc column.


Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that do not have Versaold in column F.
Dim Rng As Range, cell As Range, del As Range
Dim strCellValue As String
Set Rng = Intersect(Range("F2:F1000"), ActiveSheet.UsedRange)
For Each cell In Rng

strCellValue = (cell.Value)
If InStr(strCellValue, "VERSACOLD") > 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.DELETE
End Sub


Thanks A lot

 

[AlphaFrog]

Can you better explain what you mean by "viable to a specifc column"?

 

[FireRulerX]

Sorry it will not just check only column F

 

[AlphaFrog]

Sub Delete_Rows2()
    ' This macro deletes all rows on the active worksheet
    ' that do not have Versaold within the row.
    Dim Lastrow As Long
    
    Application.ScreenUpdating = False
    
    Lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For r = Lastrow To 1 Step -1
        If Rows(r).Find("VERSACOLD", LookAt:=xlPart, MatchCase:=False) Is Nothing Then Rows(r).Delete
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[FireRulerX]

The code worked good but the problem is I need the code to only find the word versacold in column F as there could be the word versacold in column A or B and I want them delete too.

Cheers

 

[Weaver]

Maybe:

Option Compare Text
Sub delNotFound()
    Dim searchCol, searchParam, headerRow, lastRow
    searchCol = "F"
    searchParam = "versacold"
    headerRow = 2
    lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = lastRow To headerRow + 1 Step -1
        If InStr(Cells(i, searchCol), searchParam) = 0 Then Rows(i).EntireRow.Delete
    Next i
End Sub

 

[AlphaFrog]

What you are saying is contradictory to me. I don' t understand.

This...

I need the code to only find the word versacold in column F

Is the opposite of this...

there could be the word versacold in column A or B and I want them delete too

Maybe you could give an example.

 

[FireRulerX]

If I use these smileys as an example I would want everything but row 3,4, and 6 non-delete as they have the blue question mark smiley in the 4th column. I would want all the others deleted even though some of them contain blue question smileys. so if you use the word versacold to represent blue smiley's question mark that is what I mean hope this helps.

 

[Weaver]

Did you try the code in post #6?

 

[FireRulerX]

Weaver that worked a sinch cheers