Remove Rows and Columns if text not found.

It would be easier to answer if you posted a sample file. Assuming you're cycling through all cells in your range you can use the wildcard character to test for "Fail" .. or, if the cell only contains the word, just search for it and delete the row. e.g.

Private Sub DeleteFails()
Dim cel As Range, rng As Range

    Set rng = Sheet(1).Range("A1:D100")     'set to whatever your range is.
    On Error Resume Next                    'for when the text is not found.
    Err.Clear
    Set cel = rng.Find(what:="Fail", LookIn:=xlValues, lookat:=xlWhole)
    While Err.Number = 0
        cel.EntireRow.Delete shift:=xlUp
        Set cel = rng.FindNext(cel)
    Wend
    On Error GoTo 0
    Err.Clear
    
End Sub

Hope that helps.

Sorry - I misread your post .. correction coming.

Here ya go:

Private Sub DeleteFails()
Dim i As Long
Dim booFound As Boolean
Dim cel As Range, rng As Range

    Set rng = ThisWorkbook.Worksheets(1).Range("A1:D100")     'set to whatever your range is.
    On Error Resume Next                    'for when the text is not found.
    Err.Clear
    
    'check columns first:
    For i = rng.Columns.Count To 1 Step -1
        Set cel = rng.Columns(i).Find(what:="Fail", LookIn:=xlValues, lookat:=xlWhole)
        If cel Is Nothing Then rng.Columns(i).Delete
    Next i
    
    'then rows:
    For i = rng.Rows.Count To 1 Step -1
        Set cel = rng.Rows(i).Find(what:="Fail", LookIn:=xlValues, lookat:=xlWhole)
        If cel Is Nothing Then rng.Rows(i).Delete
    Next i
    
    On Error GoTo 0
    Err.Clear
    
End Sub

If you want to search for anything containing "fail" but not necessarily the whole word - e.g. failure, or failed etc - use this in the search:

Set cel = rng.Columns(i).Find(what:="Fail", LookIn:=xlValues, lookat:=xlPart)

Hope that helps.

Originally Posted by photom

Attachment 839910

Thank you so much for your help. I've attached an example of the file, hopefully that helps.
I tried applying the script and it did a great job, however the columns lost their context. Is it possible when deleting a column/row for the following entire column to move along?
Does that make sense?

Thanks again

Who are you replying to - me or Glenn? In my example the columns shift along to the left and the rows shift up, however, since you asked to delete the entire column and row, you would lose the headers in row 1 (A1, A2 etc) and the names in the first column. To avoid this, set your rng to B2:AQ27 and use this instead of Delete:

If cel Is Nothing Then rng.Columns(i).ClearContents

... and same for rows. See attached workbook.

This might seem like a silly question, but your explanation was ambiguous.

What are your expected result in this file?

Is it just the red cells in the file, since the rows and columns of the orange cells contain no fails



or something else?