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

Hallo there :hi:


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! :hi:

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

Code1:

Code:

---

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:

Code:

---

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

---

Not tested, but try this

Code:

---

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

---

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! :clap:

the final code looks like this:

Code:

---

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

---

Quote:

---

Originally Posted by maldroid

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

---

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

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...

or

Code:

---

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

---