View Full Version : [SOLVED] Another Delete Sheets Macro

10-17-2017, 01:46 AM

I have looked for a basic method to do this but cannot seem to find it :-/

I just want to delete any sheet that is in a list on a defined sheet as a list
So list may be 20 sheet names in column A1:20, but it needs to be dynamic so I can amend the list

Code I had working before uses specific sheet name

Dim i As Integer, n As Integer
n = ThisWorkbook.Worksheets.Count

Set MyList = Sheets("Results").Range("A1")
Set MyList = Range(MyRange, MyRange.End(xlDown))

Application.DisplayAlerts = True

For i = n To 1 Step -1

On Error Resume Next
If InStr(1, Sheets(i).Name, "Test Sheet") Then Sheets(i).Delete 'named sheet works
If InStr(1, Sheets(i).Name, MyList) Then Sheets(i).Delete ' Use variable for sheet name?
On Error GoTo 0
Next I

If using line to of the InSTr, it works but deletes all sheets in workbook

Thanks for the help

10-17-2017, 02:20 AM
Sub vbax_61039_del_sheets_in_list()

Dim i As Long
Dim MyList

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

With Worksheets("Results")
MyList = .Range("A1:A" & .Range("A1").End(xlDown).Row).Value
End With

On Error Resume Next
For i = LBound(MyList, 1) To UBound(MyList, 1)
If Worksheets.Count = 1 Then Exit For
Worksheets(MyList(i, 1)).Delete
Next i

End Sub

10-17-2017, 02:32 AM
Works perfectly!

Thank you very much

10-17-2017, 04:37 AM
you are welcome.
thanks for the feedback.
pls mark the thread as solved for future references...