VBA to delete certain sheets

[VegasNath]

Is there a way to delete all sheets between sheets "start" & "end" using vba?

[HansV]

Create this procedure in a standard module:

Code: Select all

Sub DeleteSheets(FirstName As String, LastName As String)
  Dim i As Integer
  Dim f As Boolean
  Dim strName As String

  On Error GoTo ErrHandler

  Application.DisplayAlerts = False
  For i = Worksheets.Count To 1 Step -1
    strName = Worksheets(i).Name
    If strName = LastName Then
      f = True
    End If
    If f Then
      Worksheets(i).Delete
    End If
    If strName = FirstName Then
      f = False
    End If
  Next i

ExitHandler:
  Application.DisplayAlerts = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ErrHandler
End Sub

Call it like this:

Call DeleteSheets("start", "end")

Warning: this version will also delete the start and end sheets. If you don't want that, the code will have to be modified!

It is your responsibility to ensure that "end" is to the right of "start".

[VegasNath]

Thanks Hans. I need to keep start and end, deleting only the sheets between them. How do I modify please?

[HansV]

Just change the order of the code:

Code: Select all

Sub DeleteSheets(FirstName As String, LastName As String)
  Dim i As Integer
  Dim f As Boolean
  Dim strName As String

  On Error GoTo ErrHandler

  Application.DisplayAlerts = False
  For i = Worksheets.Count To 1 Step -1
    strName = Worksheets(i).Name
    If strName = FirstName Then
      f = False
    End If
    If f Then
      Worksheets(i).Delete
    End If
    If strName = LastName Then
      f = True
    End If
  Next i

ExitHandler:
  Application.DisplayAlerts = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ErrHandler
End Sub

[VegasNath]

Thanks Hans, works great!

Also, may I ask how I could create a toggle to show / hide all sheets between start & end, but in this case, including start & end?

Under usual circumstances, I know that I can do this by referencing which sheets to, or not to hide / unhide. The difference here is the sheer volume of sheets so I am wondering if there is a slicker (than my usual) approach.

TIA.

[HansV]

Here is a procedure based on the same idea:

Code: Select all

Sub ShowHideSheets(FirstName As String, LastName As String, ShowHide As Boolean)
  Dim i As Integer
  Dim f As Boolean
  Dim strName As String

  On Error GoTo ErrHandler

  Application.DisplayAlerts = False
  For i = Worksheets.Count To 1 Step -1
    strName = Worksheets(i).Name
    If strName = LastName Then
      f = True
    End If
    If f Then
      Worksheets(i).Visible = ShowHide
    End If
    If strName = FirstName Then
      f = False
    End If
  Next i

ExitHandler:
  Application.DisplayAlerts = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ErrHandler
End Sub

To hide sheets from "start" to "end":

Call ShowHideSheets("start", "end", False)

To show them again:

Call ShowHideSheets("start", "end", True)

[VegasNath]

Cool, Thanks Hans!