By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Select & Delete Sheets in Workbook using Wildcard

P: 34
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.
Jul 2 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
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.

Expand|Select|Wrap|Line Numbers
  1. Sub DeleteSheetColour(ByVal strColour As String)
  2.     Dim ws As Worksheet
  3.     Dim NLen As Integer
  4.  
  5.     NLen = Len(strColour)
  6.     For Each ws In ActiveWorkbook.Sheets
  7.         With ws
  8.             If Left(UCase(.Name), NLen) = UCase(strColour) Then
  9.                 If ThisWorkbook.Sheets.Count > 1 Then
  10.                     Application.DisplayAlerts = False
  11.                     .Delete
  12.                     Application.DisplayAlerts = True
  13.                 End If
  14.             End If
  15.         End With
  16.     Next ws
  17. End Sub
  18.  
  19. Sub TestSheetDelete()
  20.     DeleteSheetColour "Green"
  21. End Sub
HTH

MTB
Jul 4 '08 #2

P: 34
Thanks.... I appreciate your help!!
Jul 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.