PDA

View Full Version : VBA - Removing a Sheet based on a condition (else a message box)



maldroid
03-27-2018, 08:04 AM
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:

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
03-27-2018, 04:20 PM
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
03-27-2018, 11:28 PM
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:


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
03-27-2018, 11:35 PM
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!

maldroid
03-27-2018, 11:58 PM
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
03-28-2018, 12:45 AM
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