1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

delete all sheets except the first 2 and the last X amount of sheets

Discussion in 'VBA Macros' started by miming1986, Nov 1, 2017.

  1. miming1986

    miming1986 New Member

    Messages:
    14
    How do I delete all sheets except the first 2 sheets and the last X amount of sheets? I also want to have a pop-up message asking the user how many last sheets they don't want to get deleted.

    Note: 2nd sheet is hidden - so i don't want to delete the first visible, the 2nd (which is hidden) and last X amount of sheets

    Thanks so much in advance!


    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

    Last edited: Nov 1, 2017
  2. Derek McGill

    Derek McGill Active Member

    Messages:
    143
    What quantity is "X" or how is it worked out ?
  3. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,882
    See if attached file will help you. I have created a userform.
    - Click on the button to open the form.
    - Select the workbook name from drop down
    - It will show you worksheets name in the list.
    - You can delete single worksheet by double clicking in the list or to delete multiple sheets just select the sheets you need to delete and press the button.

    Note: Form is not very rigorously tested. I was just plain bored so made it. It may not be what you want.

    Attached Files:

    Thomas Kuriakose likes this.
  4. miming1986

    miming1986 New Member

    Messages:
    14
    userform can be useful but i want a command button where it will delete all sheets except the first two and the last x amount of sheets. The first two sheets are very vital (Dashboard - visible and Master - hidden) and the rest sheets vary (daily tracker). At the end of the month, these sheets need to be deleted, however, some shouldn't be because they are part of the current month. All my sheets' name except the first two are in "mmdd" format.
  5. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,882
    You can do this by using code like below:
    Code (vb):
    Public Sub DelShts()
    Dim lngLastShtCount As Long, i As Long
    lngLastShtCount = Application.InputBox("Provide sheet count to be skipped:", "Do Not Delete", , , , , , 1)
    Application.DisplayAlerts = False
    For i = ThisWorkbook.Sheets.Count - lngLastShtCount To 3 Step -1
        ThisWorkbook.Sheets(i).Delete
    Next
    Application.ScreenUpdating = True
    End Sub
    miming1986 likes this.
  6. miming1986

    miming1986 New Member

    Messages:
    14
    thanks so much shrivallabha! it worked, however, if i click cancel on the input box, the execution doesn't stop and the whole sheets get deleted except the first two and when i click ok without any input, i want the error message to be personalized like "please input the last amount of sheets to be skipped"
    Last edited: Nov 6, 2017
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,882
    Test below code and see if this is what you want.
    Code (vb):
    Public Sub DelShts()
    Dim lngLastShtCount As Long, i As Long
    Dim vbMsgRslt As VbMsgBoxResult
    UserInput:
    lngLastShtCount = Application.InputBox("Provide sheet count to be skipped:", "Do Not Delete", , , , , , 1)
    If lngLastShtCount = 0 Then
        vbMsgRslt = MsgBox("Press Yes: To Delete All Sheets Except First Two" & vbCrLf & _
                          "Press No : To go back and input number of sheets again" & vbCrLf & _
                          "Press Cancel : To cancel deletion", vbYesNoCancel)
        Select Case vbMsgRslt
        Case vbYes
            '\\ Do nothing
       Case vbNo
            GoTo UserInput
        Case vbCancel
            Exit Sub
        End Select
    End If
    Application.DisplayAlerts = False
    For i = ThisWorkbook.Sheets.Count - lngLastShtCount To 3 Step -1
        ThisWorkbook.Sheets(i).Delete
    Next
    Application.ScreenUpdating = True
    End Sub
    Thomas Kuriakose and miming1986 like this.
  8. miming1986

    miming1986 New Member

    Messages:
    14
    Thanks much! But i have a question, is it not possible to terminate the input box after clicking cancel? Why do we have to have another box to cancel it? And for clicking ok with an empty string, can we have a personalized message rather than the default "We found a problem with this formula..." or we can just terminate it.
    Sorry my VBA knowledge is very minimal.
    Last edited: Nov 6, 2017
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,882
    Let's just say this is the easy path ;) The result returned for cancel and 0 input are the same and I did not dig further.
    miming1986 likes this.

Share This Page