Delete Worksheet from List Macro

[Closed]
Report
Posts
1
Registration date
Saturday November 23, 2013
Status
Member
Last seen
November 23, 2013
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
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 reply

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
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