VBA - Removing a Sheet based on a condition (else a message box)

[maldroid]

Hallo there


please, can you help me with this issue? Basically I have to remove a sheet "Clock" from the workbook with ca 60 Sheets. But only if there are two conditions fulfilled:


1) On each worksheet there has to be text "out" in the cell J2
2) The number of rows on each worksheet has to be more than 2 (in column A)


If these conditions are fulfilled, i want to delete an entire sheet "Clock" with confirmation (without property .DisplayAlerts). The sheet "Clock" is always the first, so after the removal I want to land on the second sheet...


All sheets have to have J2="out", if there is anything else or if there are less than 3 rows on the sheet, i want a message box with text "J2<>out or rowscount<3" + name of the sheet (it could happen on more than 1 sheet and I need to know on which does the error occure - it doesn't matter if it's displayed in one message box or more after each other) and just stop, so I can make correction and run the macro again.


I'm new here so I hope it makes sence. Thank you all for your help!

I've tried these and they don't work as I need to...

Code1:

Sub WorksheetLoop()
    Dim ws As Worksheet
        For Each ws In Worksheets
            ws.Activate
            If ws.Range("J2") = "in" Or ws.Cells(Rows.Count, 1).End(xlUp).Row < 3 Then
                MsgBox "Sheet: " & ActiveSheet.Name & vbNewLine & "J2<>out or rowscount<3"
                Exit For
            Else
                Sheets("Clock").Delete
            End If
        Next ws
End Sub

Code2:

Sub WorksheetLoop()
     ' Declare Current as a worksheet object variable.
     Dim WS_Count As Byte
     Dim I As Byte
     Dim H As Byte
     H = 0
     WS_Count = ActiveWorkbook.Worksheets.Count
     ' Loop through all of the worksheets in the active workbook.
         For I = 1 To WS_Count


            If ActiveWorkbook.ActiveSheet.Range("J2") = "in" Or ActiveWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row < 3 Then
                H = H + 1
                MsgBox "J2<>out or rowscount<3" & vbNewLine & "Sheet: " & ActiveWorkbook.ActiveSheet.Name
            End If
         
         Next I


    If H = 0 Then
     Sheets("Clock").Delete
    End If
End Sub

[Bob Phillips]

Not tested, but try this

Sub WorksheetLoop()
Dim ws As Worksheet
Dim act As Boolean
    
    act = True
    
    For Each ws In Worksheets
    
        With ws
        
            If .Range("J2").Value <> "out" Or .Cells(.Rows.Count, "A").End(xlUp).Row < 3 Then
        
            MsgBox "Sheet: " & .Name & vbNewLine & "J2<>out or rowscount<3"
            act = False
            Exit For
        End If
    Next ws
    
    If act Then Worksheets("Clock").Delete
End Sub

[maldroid]

Thank You very much, works like a charm... the only litttle thing was a missing "End With" right after "End If"...

I thank you from the very bottom of my rehydration unit!

the final code looks like this:

Sub WorksheetLoop()
Dim ws As Worksheet
Dim act As Boolean
    
    act = True
    
    For Each ws In Worksheets
    
        With ws
        
            If .Range("J2").Value <> "Out" Or .Cells(.Rows.Count, "A").End(xlUp).Row < 3 Then
        
                MsgBox "Sheet: " & .Name & vbNewLine & "J2<>out or rowscount<3"
                act = False
                Exit For
            End If
        End With
    Next ws
    
    If act Then Worksheets("Clock").Delete
End Sub

[Bob Phillips]

I thank you from the very bottom of my rehydration unit!

Glad you caught my boob, but you've got to explain that bit!

[maldroid]

Kryten's quote from Red Dwarf TV series - episode Camille. In our Country it's really popular among the geeks :-) I hope it doesn't violate this forum's terms and conditions...

[snb]

or

Sub M_snb()
  For j=1 to sheets.count
     if sheets(j).Cells(2,10)<> "Out" or sheets(j).Cells(Rows.Count, "A").End(xlUp).Row < 3 Then exit for
  Next

  If j>sheets.count Then sheets("Clock").Delete
End Sub