Worksheets Delete Macro From Master List

[marklee800]

Hi All,

I am trying to delete some worksheets within a workbook from a list containing the names of worksheets to be deleted, listed in column B2:B30, in sheet titled " Sheetlist". I obtained the following code from a thread, the code seems to work if column B in the specified range are all populated. If it is empty, as in the example, the code does no work.

In "SheetList" sheet, cell A2 onwards in column A contain all the names within the workbook. Cell B2 onwards in column B to cell B30 will be the area which I specify the sheets I need to delete.


Can you please help to modify the code so that the macro will loop through each cell from B2:B30 eventhough it is empty, and delete worksheets name which are listed?

Thanks.

Mark

Sub DeleteSheets()
Dim i As Long
For i = 2 To 30

Sheets("SheetList").Select
Application.DisplayAlerts = False
Worksheets(CStr(ActiveSheet.Cells(i, 2).Value)).Delete
Application.DisplayAlerts = True
Next i
End Sub

[NoS]

Hello Mark

I'd use this function that I found somewhere on the net a long time ago

Private Function SheetExists(sname) As Boolean
    ' Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function

then use a macro like this

Sub DeleteSheetsInColB()
    Dim rng As Range    'the range containing sheet names to be deleted
    Dim cel As Range    'each individual cell within that range of sheet names
    
'set the range of sheet names to be deleted
Set rng = Sheets("Sheetlist").Range("B2:B30")

'prevent Excel's warning about deleting sheets
Application.DisplayAlerts = False

'step through the list of sheet names
For Each cel In rng
'check that the cell is not empty
    If Len(cel.Value) > 0 Then
        'cell contains name of a sheet, check if sheet actually exists
        If SheetExists(cel.Value) = True Then
            'delete the sheet
            Sheets(cel.Value).Delete
        End If
    End If
Next cel
    
'turn warnings back on
Application.DisplayAlerts = True

End Sub

Copy both of these codes into a new module and give a try.

Good luck with your project. Hope this is of some assistance.