delete specific sheets based on user input

[MKLAQ]

hi all,

I search for macro to delete specific sheets or all the sheets except the first sheet by using inputbox , so when run inputbox write 2,3,4 then should delete the second and third and fourth sheets whatever are the sheets' names , and if I write "ALL" then delete all sheets except the first sheet
thanks

 

[johnnyL]

Hi @MKLAQ. There is a problem with your logic there. When a sheet is deleted, the index numbers of the sheets are recalculated for the remaining sheets. So if you were to enter 2,3,4, thinking that you would be deleting the 2nd, 3rd, and 4th sheets, you would actually be deleting the 2nd, 4th, and 6th sheet. You would actually have to enter 2,2,2 to delete the 2nd, 3rd, and 4th sheet. Because of that potential confusion, I came up with some code that will delete the sheets one at a time for you:

Sub SheetDeleter()
'
'   This will delete workbook sheets according to the index number of the sheet entered by the user
'
    Dim UserInput   As Long
'
    UserInput = Application.InputBox("Enter Sheet index to Delete", Type:=1)            ' Type:=1 means integer
'
    Do Until UserInput = False
        If UserInput <> False Then
            Application.DisplayAlerts = False                                           ' Turn display alerts off
'
            ThisWorkbook.Sheets(UserInput).Delete                                       ' Delete sheet
'
            Application.DisplayAlerts = True                                            ' Turn display alerts back on
'
            UserInput = Application.InputBox("Enter Sheet index to Delete", Type:=1)    ' Type:=1 means integer
        End If
    Loop
End Sub

If you wanted to start rapidly deleting sheets starting with the 2nd sheet, you could start the script and when the popup box appears, you could just start hitting 2,Enter,2,Enter,2,Enter, etc.

Let us know if that works for you.

 

[MKLAQ]

You would actually have to enter 2,2,2 to delete the 2nd, 3rd, and 4th sheet.

it doesn't work it gives the reference is not valid

your code just delete each sheet separately

last note When delete specific sheets , they will not be arranged consecutively I mean if I decide deleting specific sheets first sheet and fourth sheet and seventh sheet together

 

[Fluff]

How about

Sub MKLAQ()
   Dim Shts As Variant
   Dim i As Long
   
   Shts = InputBox("Please enter sheet numbers")
   If Shts = "" Then Exit Sub
   If LCase(Shts) = "all" Then
      For i = Sheets.Count To 2 Step -1
         Application.DisplayAlerts = False
         Sheets(i).Delete
         Application.DisplayAlerts = True
      Next i
   Else
      Shts = Split(Shts, ",")
      For i = 0 To UBound(Shts)
         Shts(i) = Sheets(CLng(Shts(i))).Name
      Next i
         Application.DisplayAlerts = False
         Sheets(Shts).Delete
         Application.DisplayAlerts = True
   End If
End Sub

 

[johnnyL]

it doesn't work it gives the reference is not valid

your code just delete each sheet separately

Yes. That is what I stated. The code I submitted is set up to delete one sheet at a time.

The code @Fluff submitted will handle multiple sheets as you requested. @Fluff addressed the issue I mentioned above by converting the numbers to actual sheet names prior to deletion. Very nice!

 

[MKLAQ]

@Fluff great solution ! this is exactly what I want
thanks so much

 

[Fluff]

Glad to help & thanks for the feedback.