Delete Rows

[Sontauzo]

Greetings,

I have been looking through the forums for an example, but i can't find exactly what I am looking for. I am looking for VBA to run through my sheet and remove rows that contain a certain keyword (override) in a certain column (F). The thing that is tripping me up is that the keyword is not the only text in the cell. I have found examples of code that deletes the row if the entire contents of the cell matches the keyword, but not if the keyword is part of a larger string. If it helps "override' is always the last word in the cell.

I hope that makes sense. Thanks! BWL

 

[gallen]

Hello,

Make sure you test this on a COPY of your data as it will delete without UNDO being possible.

Paste this into the code window of the sheet you wish it to work on.

Sub DeleteRows()

    Dim lr As Long 'last row number
    Dim fr As Long 'first row number
    Dim l As Long 'counter
    Dim sFind As String
    
    lr = Range("F" & Rows.Count).End(xlUp).Row
    fr = 2 'first row of data
    
    sFind = "override"
    
    For l = lr To fr Step -1 'count backwards from bottom up
        If InStr(1, Range("F" & l), sFind) > 0 Then 'is the word override anywhere in the cell?
            Range("F" & l).EntireRow.Delete
        End If
    Next l
    
End Sub

 

[Sontauzo]

Thank you!!!! This works exactly the way I was hoping!

 

[Fluff]

Another option

Sub delrws()

   With ActiveSheet
      If .AutoFilterMode Then AutoFilterMode = False
      .Range("F:F").AutoFilter 1, "*overide"
      .Range("F2", .Range("F" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End If
End Sub

 

[louisH]

Another funny & original option :

Sub DeleteRows()

    Dim celselection As String
    celselection = ""
    
    lastr = Range("F" & Rows.Count).End(xlUp).Row
    
    For Each cel In Range("F1:F" & lastr)
    
        If cel.Value Like "*override*" Then
            celselection = celselection & "," & cel.Address
        End If
        
    Next cel
    
    celselection = Right(celselection, Len(celselection) - 1)
    
    Range(celselection).EntireRow.Delete
    
End Sub

 

[bs0d]

Greetings,

I have been looking through the forums for an example, but i can't find exactly what I am looking for. I am looking for VBA to run through my sheet and remove rows that contain a certain keyword (override) in a certain column (F). The thing that is tripping me up is that the keyword is not the only text in the cell. I have found examples of code that deletes the row if the entire contents of the cell matches the keyword, but not if the keyword is part of a larger string. If it helps "override' is always the last word in the cell.

I hope that makes sense. Thanks! BWL

In VBA, you can use the inStr() function to search the cell for the desired text:

Sub searchCell()
Dim chkVal As Integer
Dim mySheet As Worksheet
Set mySheet = Sheets("Sheet1")

chkCell = mySheet.Range("F25").Value

chkVal = InStr(1, chkCell, "-FIND ME-")

If chkVal > 0 Then
    mySheet.Rows(25).Delete
End If
End Sub

Alternatively you can use a formula to identify rows that need to be deleted via:

=ISNUMBER(SEARCH("-FIND ME-", ReferenceCell, 1))