Select & Delete Sheets in Workbook using Wildcard

Hello,

I have an Excel workbook with ~ 21 sheets in it.
Let's say 7 are named "Red1", "Red2", "Red3", ect....
Another 7 are named for "Green1, 2, 3", ect...
and the last 7 are named "Blue1, 2, 3", ect...

I'm trying to write a very basic code to go though the entire Workbook, select and then delete all sheets with the name containing "Red*" (regardless of the following number). So far I have:

Sub DeleteRed()
Dim ws As Worksheet
Dim x As Integer
x = 1
For Each ws In ActiveWorkbook.Sheets
x = x + 1
For Each ws In ActiveWorkbook.Sheets
If Left(ws.Name, 3) = "Red" Then
ActiveSheet.Delete
End If
Next ws
End Sub

Yes I am a novice, and any help would be appreciated!

Thank you.

MikeTheBike

Expert

256 Posts

July 4th, 2008
11:52 AM
#2

Re: Select & Delete Sheets in Workbook using Wildcard

Hi

On the basis that the 'Colour' (or any other string) is always at the begining of the sheet name, then this will delete any name series you care to create.

Note: this will delete sheets without notification. If that is a problem just delete the line
Application.DisplayAlerts = False
from the code

Also it will not delete the last sheet (it will throw an error if you try and delete every sheet).

The UCase() functions make this not case sensitive, again remove these if you want case sensitivity.

Code: ( text )

Sub DeleteSheetColour(ByVal strColour As String)
Dim ws As Worksheet
Dim NLen As Integer
NLen = Len(strColour)
For Each ws In ActiveWorkbook.Sheets
With ws
If Left(UCase(.Name), NLen) = UCase(strColour) Then
If ThisWorkbook.Sheets.Count > 1 Then
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End If
End If
End With
Next ws
End Sub
Sub TestSheetDelete()
DeleteSheetColour "Green"
End Sub

HTH

MTB

Last edited by MikeTheBike : July 4th, 2008 at 11:54 AM. Reason: More info