Delete Worksheet from List Macro

Closed
marklee800 Posts 1 Registration date Saturday November 23, 2013 Status Member Last seen November 23, 2013 - Nov 23, 2013 at 12:09 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 26, 2013 at 11:50 AM
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 sheet 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.

A B
1 List of Sheets SHeets to Delete
2 Sheet A
3 Sheet B Sheet B
4 Sheet C
5 Sheet D Sheet D
6 Sheet E


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

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 26, 2013 at 11:50 AM
Hi Mark,

I added a line which checks if the cell is empty or not.

Here you go:
Sub DeleteSheets()
Dim i As Integer
For i = 2 To 30
Sheets("SheetList").Select
If Cells(i, 2).Value = vbNullString Then GoTo Next_i
Application.DisplayAlerts = False
Worksheets(Cells(i, 2).Value).Delete
Application.DisplayAlerts = True
Next_i:
Next i
End Sub
Best regards,
Trowa
0