VBA - Find text and Delete Entire Row in Multiple Sheet

[billylui9]

Please HELP write a VBA to finding a Text in Multiple Sheet and delete the entire row if the text is found.

This String will appear many time,

I.e. if the string "BONNIE" is present in any cell in any string position in any Sheet the entire row will be deleted.

URGENT, Many thanks

 

[Michael M]

Is "BONNIE" in any particular column ?
Is it the only text in the cell OR is it part of a string ?
Are there sheets that are not to be included in the search ?

 

[Logit]

This is one of many methods :

Option Explicit

Sub FindInLists()
     '
     ' Select FindInLists Macro
     '
    
    Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("Sheet1") '// Enter the exact sheet names of sheets to be searched
    
    SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)
    
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A2:Z10000")  'EDIT RANGE AS REQUIRED
                Set r = .Find(what:=SrchStrg, After:=.Range("A1"))   'find the cell whose value is equal to SrchStrg and activate it
                If Not r Is Nothing Then
                    'ws.Activate: r.Activate
                    r.EntireRow.Delete
                    
                'PUT YOUR CODE HERE TO WRITE THE TICKER DATA SOMEWHERE FOR REVIEW
                    
                ElseIf r Is Nothing Then
                    MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
                End If
            End With
        End If
    Next
    
End Sub

 

[Michael M]

@Logit Maybe substitute ???

With ws.Range("A2:Z10000")

FOR

With ws.UsedRange

 

[Logit]

That works also !

 

[billylui9]

Is "BONNIE" in any particular column ? YES, could be any cell in any sheet
Is it the only text in the cell OR is it part of a string ? Indiviual text or part of string
Are there sheets that are not to be included in the search ? Included

 

[Michael M]

I believe the solution provided by @Logit should do the trick !

 

[billylui9]

This is one of many methods :

Option Explicit

Sub FindInLists()
     '
     ' Select FindInLists Macro
     '
   
    Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("Sheet1") '// Enter the exact sheet names of sheets to be searched
   
    SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)
   
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A2:Z10000")  'EDIT RANGE AS REQUIRED
                Set r = .Find(what:=SrchStrg, After:=.Range("A1"))   'find the cell whose value is equal to SrchStrg and activate it
                If Not r Is Nothing Then
                    'ws.Activate: r.Activate
                    r.EntireRow.Delete
                   
                'PUT YOUR CODE HERE TO WRITE THE TICKER DATA SOMEWHERE FOR REVIEW
                   
                ElseIf r Is Nothing Then
                    MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
                End If
            End With
        End If
    Next
   
End Sub

Thanks for Helping, but...

Only 1st Sheet 1st "Bonnie"'s row deleted, the following matched case not affect as attach.

 

[Michael M]

Try using

Option Compare Text
Sub MM1()
Dim ws As Worksheet, cell As Range
For Each ws In Worksheets
    With ws
        For Each cell In .UsedRange
            If InStr(cell, "Bonnie") Then
            .Rows(cell.Row).Delete
            End If
        Next cell
    End With
Next ws
End Sub

 

[billylui9]

Try using

Option Compare Text
Sub MM1()
Dim ws As Worksheet, cell As Range
For Each ws In Worksheets
    With ws
        For Each cell In .UsedRange
            If InStr(cell, "Bonnie") Then
            .Rows(cell.Row).Delete
            End If
        Next cell
    End With
Next ws
End Sub

It work for my Sample Excel and delete text "Bonnie" in three different Sheet, but dosen't work on my working file and show :

"type mismatch (Error 13)"

I have no idea what happen ( Actaully, it work few time when I reduce no. of Sheet and Row of Content )