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!

 

[pboltonchina]

This works for me

Option Explicit
Sub deletesheet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        If Range("E2").Value = "" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
End Sub

 

[Asator]

Sub sheetdel()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
With ActiveSheet
    If .Cells(1, 4).Value = "" Then
        .Delete
    End If
End With
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[hiker95]

markf5998,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteSheets()
' hiker95, 03/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=536882
Dim a As Long, SN As String
With Application
  .ScreenUpdating = False
  .DisplayAlerts = False
  For a = 1 To 50 Step 1
    SN = a
    If Worksheets(SN).Range("E2") = "" Then Worksheets(SN).Delete
  Next a
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
End Sub

Then run the DeleteSheets macro.

 

[markf5998]

Thank you both for the replies!

I can't seem to get either one to work the way I has hoping...

The first response deletes some sheets, but none of them were those named "1" "2" "3", etc. Is it possible to make it only look at the numbered tabs and ignore the others?

The second code only deleted the tab that the button to run the macro was on.

Thanks again for the replies!

 

[hiker95]

markf5998,

Did you try my macro code?

 

[Warship]

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

 

[markf5998]

Hiker95 - Thanks for the post...but when I run the marco, it doesn't do anything...any ideas what could be wrong?

Cell E1 is merged with F1 on these sheets if that makes a difference, but I would n't think so since when I put the cursor on the cell, the reference box says "E1" still...

Mark

 

[markf5998]

Warship - I tried your macro, but when I run it it doesn't delete any sheets...any ideas what I could be doing wrong?

 

[hiker95]

markf5998,

In my test environment with 50 + worksheets, those that had E1 blank were deleted.


Can you post a workbook with only two worksheets; one with the merged cell E1 blank, and one with the merged cell E1 not blank.

You can upload this workbook to www.box.net and provide us with a link to your workbook.