Delete sheet if the row 2 cells don't contain a specific status

[badrhari]

Hi all & Happy New Year,

Quite new to using VBA and facing a challenge.
I have an Excel file with multiple sheets and the same structure, as below. I'm interested in working only in row 2 range

Name	A	B	C
Status	Created	Hold	Ready
Date	today	today	today

What I want is to go in each sheet and if the row 2 range doesn't contain Ready status (or it contains only Hold and Created) on any cell, delete the whole sheet. If it contains, just keeps the sheet.
There some piece of code I've found and adapted but doesn't work as expected.

Sub test()
Dim i As Integer
Application.DisplayAlerts = False
On Error Resume Next
For i = Worksheets.Count To 1 Step -1
Sheets(i).Select
If Not Range("A2").Value = "Ready" Then
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True
End Sub

Any help or suggestions will be much appreciated.

Many thanks.

 

[StephenCrump]

Welcome to the Forum, and Happy New Year to you too.

The code below will delete all worksheets that don't contain "Ready" in row 2. Is this what you wanted?

Sub Test()
    
    Dim ws As Worksheet
    
    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match("Ready", ws.Range("2:2"), 0)) Then ws.Delete
    Next ws
      
    Application.DisplayAlerts = True

End Sub

 

[badrhari]

Welcome to the Forum, and Happy New Year to you too.

The code below will delete all worksheets that don't contain "Ready" in row 2. Is this what you wanted?

Sub Test()
   
    Dim ws As Worksheet
   
    Application.DisplayAlerts = False
   
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match("Ready", ws.Range("2:2"), 0)) Then ws.Delete
    Next ws
     
    Application.DisplayAlerts = True

End Sub

Many thanks Stephen. This works just perfect for what I need.

Cheers.