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

[RandomUserCode]

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

 

[Jon von der Heyden]

Hi

When deleting rows by criteria using VBA it is generraly advisable to use an AutoFilter. It spares several deletions because it deletes all matching rows in a sheet in a single shot.

Also, you cannot delete rows by looping forwards (as you code does with "For Each") - or rather you run a risk of missing qualifying rows. This is because the grid is changing. Say both rows 3 and 4 match, when you are on row 3 it will delete, and then the loop will move to row 4, but the original row 4 will now occupy row 3 and thus be missed.

Untested, but try:

Public Sub DelRows()
    Const strTargetSheets   As String = "Sheet1,Sheet2"
    Const strMatch          As String = "StringName"
   
    Dim wksTarget           As Excel.Worksheet
   
    For Each wksTarget In Sheets(Split(strTargetSheets, ","))
        With wksTarget
            If .FilterMode Then .ShowAllData
            With .Range(.Range("F1"), .Range("F" & .Rows.Count).End(xlUp))
                Call .AutoFilter(Field:=1, Criteria1:=strMatch)
                .Offset(1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
                'Call .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                Call .AutoFilter
            End With
        End With
    Next wksTarget
End Sub

This will flag rows for deletion in red. You can comment out the ColorIndex line of code and remove the comment from the Delete row below if this produces the desired results.

 

[RandomUserCode]

Hi

When deleting rows by criteria using VBA it is generraly advisable to use an AutoFilter. It spares several deletions because it deletes all matching rows in a sheet in a single shot.

Also, you cannot delete rows by looping forwards (as you code does with "For Each") - or rather you run a risk of missing qualifying rows. This is because the grid is changing. Say both rows 3 and 4 match, when you are on row 3 it will delete, and then the loop will move to row 4, but the original row 4 will now occupy row 3 and thus be missed.

Untested, but try:

Public Sub DelRows()
    Const strTargetSheets   As String = "Sheet1,Sheet2"
    Const strMatch          As String = "StringName"
  
    Dim wksTarget           As Excel.Worksheet
  
    For Each wksTarget In Sheets(Split(strTargetSheets, ","))
        With wksTarget
            If .FilterMode Then .ShowAllData
            With .Range(.Range("F1"), .Range("F" & .Rows.Count).End(xlUp))
                Call .AutoFilter(Field:=1, Criteria1:=strMatch)
                .Offset(1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
                'Call .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                Call .AutoFilter
            End With
        End With
    Next wksTarget
End Sub

This will flag rows for deletion in red. You can comment out the ColorIndex line of code and remove the comment from the Delete row below if this produces the desired results.

THANKS!'