VBA to delete certain sheets
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
VBA to delete certain sheets
Is there a way to delete all sheets between sheets "start" & "end" using vba?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to delete certain sheets
Create this procedure in a standard module:
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".
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 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".
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: VBA to delete certain sheets
Thanks Hans. I need to keep start and end, deleting only the sheets between them. How do I modify please?
Last edited by VegasNath on 04 Aug 2010, 20:55, edited 2 times in total.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to delete certain sheets
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: VBA to delete certain sheets
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.
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to delete certain sheets
Here is a procedure based on the same idea:
To hide sheets from "start" to "end":
Call ShowHideSheets("start", "end", False)
To show them again:
Call ShowHideSheets("start", "end", True)
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
Call ShowHideSheets("start", "end", False)
To show them again:
Call ShowHideSheets("start", "end", True)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.