delete multiple sheets

  • I am trying to delete multiple sheets
    They are named "LS1", "LS2", "LS3" etc. I was trying to use a wild card, but that did not work. Here is what I have so far.



  • Hi,

    Following procedure shows one approach to solve it:

    Option Explicit

    Sub Delete_Sheets()
    Dim vaNames As Variant

    Application.DisplayAlerts = False

    vaNames = Array("LS1", "LS2", "LS3")


    Application.DisplayAlerts = True

    End Sub

  • if you want a bit more flexibility with regards to the sheetnames and 'wildcards' you use you could use the following (tested) macro:

    Sub DelSheets()
    Dim SheetNames() As String
    Dim SheetCount As Integer
    Dim i As Integer
    Dim wildcard As String
    Dim length As Integer

    wildcard = InputBox("Type in 'wildcard' characters for the sheets you want to delete", "Deletion of Worksheets")
    length = Len(wildcard)

    Application.DisplayAlerts = False
    SheetCount = ActiveWorkbook.Sheets.Count
    ReDim SheetNames(1 To SheetCount)

    For i = 1 To SheetCount
    SheetNames(i) = ActiveWorkbook.Sheets(i).Name
    Next i

    For i = 1 To SheetCount
    If Left(SheetNames(i), length) = wildcard Then
    End If
    Next i
    Application.DisplayAlerts = True
    End Sub

    Best Regards, Manfred

  • manfredm
    Thanks, that works to delete one at a time, but is there a way to add a wild card to the end of the string to delete all of the worksheets that begin with LS ? Or do I have to delete them one at a time?


  • How about this?

    Sub DelSh()

    Dim i As Integer, vNames() As Variant, wSh As Worksheet

    i = -1
    For Each wSh In ThisWorkbook.Worksheets
    If Left(wSh.Name, 2) = "LS" Then
    i = i + 1
    ReDim Preserve vNames(i)
    vNames(i) = wSh.Name
    End If
    Next wSh

    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    End Sub


    Richard Fuller

  • hi hutchval,
    I have tested the macro again and it works both on Win98+O2k and Win2000+O2k.
    You just have to put 'LS' into the Input-box (case sensitive). I have allowed for an Input box because one is not stuck with a hard-coded value, you can also delete for 'wildcards' with one or three or more characters (i.e. 'L', 'LLL', 'LLS', ...)
    Regards, Manfred

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!