Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Delete all sheets that meets my critera without prompt

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Delete all sheets that meets my critera without prompt

    I'm looking for a script to delete any sheets that meets the following critera and without promtp:
    If sheets contains the text: "PrixMax" "6po" "Quote" Then deleted them

    Sheet name can be "PrixMax-250", "6po-Viny", "Quote-101018"
    Along with those sheets, my workbook contains a "Master" sheets and "templates" which will remain

    Thank you

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    915
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    try this code:
    Code:
    Dim nm As String
    Dim Namesar(1 To 3) As String
    Namesar(1) = "PrixMax"
    Namesar(2) = "6po"
    Namesar(3) = "Quote"
    For i = 1 To Worksheets.Count
     nm = Worksheets(i).Name
       For j = 1 To 3
        testf = InStr(nm, Namesar(j))
        If testf <> 0 Then
          Application.DisplayAlerts = False
          Worksheets(i).Delete
          Application.DisplayAlerts = True
          Exit For
         End If
       Next j
    Next i
    Last edited by offthelip; Oct 7th, 2019 at 12:27 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    it gives me error...
    If I have 1 sheet to delete or none at all, it's fine
    If I have 2 or more sheets that meets the criteria it may delete only 1, sometimes 2, then it gives me:

    Run-time error "9"
    Subscript out of range

    and it's stuck at "nm = Worksheets(i).Name" when I enter the debug menu

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    915
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    I think that is because as the worksheets get deleted the worksheet count goes down so the loop goes too far, so we need to put acheck in at the end of the loop,
    try this:
    Code:
    Dim nm As String
    
    Dim Namesar(1 To 3) As String
    Namesar(1) = "PrixMax"
    Namesar(2) = "6po"
    Namesar(3) = "Quote"
    For i = 1 To Worksheets.Count
     nm = Worksheets(i).Name
       For j = 1 To 3
        testf = InStr(nm, Namesar(j))
        If testf <> 0 Then
          Application.DisplayAlerts = False
          Worksheets(i).Delete
          Application.DisplayAlerts = True
          Exit For
         End If
       Next j
    if i= worksheets.count then
     exit for
    end if
    Next i
    Last edited by offthelip; Oct 7th, 2019 at 02:29 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    Another option
    Code:
    Sub vlacombe()
       Dim Ary As Variant
       Dim Ws As Worksheet
       Dim i As Long
       
       Application.DisplayAlerts = False
       Ary = Array("PrixMax", "6po", "Quote")
       For Each Ws In Worksheets
          For i = 0 To UBound(Ary)
             If Ws.Name Like Ary(i) & "*" Then
                Ws.Delete
                Exit For
             End If
          Next i
       Next Ws
       Application.DisplayAlerts = True
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    How about

    Code:
    Sub DeleteSeheets()
      Dim sh As Worksheet, n As String
      Application.DisplayAlerts = False
      For Each sh In Sheets
        n = LCase(sh.Name)
        If n Like LCase("*PrixMax*") Or n Like LCase("*6po-Viny*") Or n Like LCase("*Quote-101018*") Then
            sh.Delete
        End If
      Next
    End Sub
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    Thanks all,

    So many options! They all work now, I just picked one
    I think Dante's version is missing the DisplayAlert = True at the end

    Regards,

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    Quote Originally Posted by vlacombe View Post
    Thanks all,

    So many options! They all work now, I just picked one
    I think Dante's version is missing the DisplayAlert = True at the end

    Regards,
    Youre welcome and thanks for the feedback.

    The line DisplayAlert = True at the end of the code is not necessary since the default value is True. Then when the code ends, the value returns to True.
    Regards Dante Amor

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Oct 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all sheets that meets my critera without prompt

    Quote Originally Posted by DanteAmor View Post
    Youre welcome and thanks for the feedback.

    The line DisplayAlert = True at the end of the code is not necessary since the default value is True. Then when the code ends, the value returns to True.
    Thanks for the info Dante, I'm learning everyday
    Altho im sure that was probably basic stuff hehehe

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •