VBA to Remove Unused Sheets

[markf5998]

Hi! I have a workbook that has sheets named "1" through "50", along with a summary tab and some other miscellaneous sheets. I'm trying to find a way to remove all tabs in the "1" through "50" range if cell E2 is blank.

For most of my problems I've been able to find code to copy/paste, but this one has been tough to find. Any help would be greatly appreciated!

Thanks!

 

[markf5998]

Ok, rookie mistake on my part...I got the code from Hiker & Warship to work. The problem was that a macro that runs before this one was leaving a 0 in cell e2 on all of the sheets...so by modifying your code to delete sheets where e2="0", it worked!

Thank you all again for the quick replies!

I might be pushing my luck, but would it be possible to look for the highest numbered worksheet between 1 and 50 where cell e2 is not equal to 0 and delete everything after that? For example, if sheets 1 and 49 had a value other than 0 in e2, and 2-48 and 50 were blank, only the worksheet named "50" would be deleted?

Thank you all again...this forum is always so helpful...hopefully after copying/pasting and looking at how you all are writing these codes I can get to a point where I can help some people too!

 

[Warship]

remove all tabs in the "1" through "50" range if cell E2 is blank.

E2 or E1??
My code checks E2
Merged doesn't effect my tests.

 

[markf5998]

E2 is what I wanted to reference...I got your code to work after I realized my mistake (previous post) and changed it to look for a zero in E2 rather than blank.

Thanks again!

 

[Warship]

Sub DeleteSheetsWithE2blank()
    Dim x As Integer, z As Integer
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For z = 50 To 1 Step -1
        If Sheets(CStr(z)).Range("E2") = 0 Then Exit For
    Next z
    For x = z + 1 To 50
        Sheets(CStr(x)).Delete
    Next x
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

 

[markf5998]

Perfect, thank you!!