VBA search for string in a specific column in two sheets, and delete if found-CodePudding

hope someone can help me fix this code. Want to look for a string "StringName" in column F (the string will always be in column F). I have tried to make an array of the two sheets, and then loop through them and find the string i want to delete. If the string is found in one or both of the sheets, then the entire row should be deleted.

I want to do this with 4 more strings, and havent thought on how to do it yet. Would it be better to just find the strings i need to keep which is "hello" and "goodbye", and then say everything that doesn't match those two string, delete? Hope someone can help

Sub test1()

    Dim sheetArray As Variant
    Dim ws As Variant
    Dim targetCell As Range

    sheetArray = Array("Sheet1", "Sheet2")

    For Each ws In sheetArray
        With Worksheets(ws)
            For Each targetCell In Range("F:F")
                If InStr(targetCell, "StringName") Then
                    targetCell.EntireRow.delete
                End If
            Next targetCell
        End With
    Next ws

End Sub

CodePudding user response：

I used autofilter and delete rows the visible rows. if there is no header, no need of offset. I used string doesn't match criteria in sheet1 and string match criteria in Sheet2 in the code

 Sub autofilter_Remove()
    
    Dim wb As Workbook
    Dim ws1, ws2 As Worksheet
    Dim Rng1, Rng2 As Range
    
    
    Set wb = ThisWorkbook
    Set ws1 = wb.Worksheets("Sheet1")
    Set ws2 = wb.Worksheets("Sheet2")
    
    Set Rng1 = ws1.Rows(1)
    Set Rng2 = ws2.Rows(1)
    
    Stringname = "hello"
    
    '6 for F column
    Rng1.AutoFilter Field:=6, Criteria1:="<>*" & Stringname & "*"
    
    ws1.UsedRange.Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete
        
    ws1.Cells.AutoFilter
    
    
    Rng2.AutoFilter Field:=6, Criteria1:="*" & Stringname & "*"
    
    ws2.UsedRange.Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete
        
    ws2.Cells.AutoFilter
    
    
    End Sub

CodePudding user response：

Use If ... And ... or If ... Or .... Example below, I went for just keeping 'hello' and 'goodbye' as you suggest because the code's shorter/simpler that way.

Sub answer1()
Dim sheetArray As Variant, ws As String, a As Long

sheetArray = Array("Sheet1", "Sheet2")

For Each ws In sheetArray
    With Worksheets(ws)
        For a = .UsedRange.Rows.Count   UsedRange.Row -1 to .UsedRange.Row step -1 'counting backwards/upwards because we're deleting rows, counting forward/down would end up skipping some rows.
            If Not .Cells(a, 6) Like "*hello*" _
            And Not .Cells(a, 6) Like "*goodbye*" Then 'the 6 refers to column F
                .Rows(a).Delete
            End If
        Next
    End With 'Worksheets(ws)
Next ws

End Sub