Case Statement with Wild Cards

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
Hi,

I'm trying to delete sheets that have specific names or similar (e.g. delete "Sheet1" and "Sheet1 (2)") I am using the following code:

Code:
For Each ws In Worksheets
    Select Case ws.Name
        Case "Remaining*", _
                "No_Break*", _
                "Listed_Instruments*", _
                "Net_Zero_Difference*", _
                "One_Dodge_Many_FO*", _
                "Many_Dodge_One_FO*", _
                "Materiality*"
                ws.Delete
    End Select
Next ws
But whilst it is working without any breaks, it is not deleting the sheets are required. The above are the only sheets that need to be deleted.

Could someone explain why it's not working please?

Thanks in advance,
Jack
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
That is looking at the literal values. To use wildcards you need something like

Code:
For Each ws In Worksheets
    If ws.Name Like "Remaining*" Or ws.Name Like "No_Break*" _
                Or ws.Name Like "Listed_Instruments*" _
                Or ws.Name Like "Net_Zero_Difference*" _
                Or ws.Name Like "One_Dodge_Many_FO*" _
                Or ws.Name Like "Many_Dodge_One_FO*" _
                Or ws.Name Like "Materiality*" Then ws.Delete
    End If
Next ws
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Sorry, I don't know a way of using wildcards in Select Case statements.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
No worries, see if anyone else posts something. It seemed like something suitable for CASE!
Thanks Peter,
Jack
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can do this:

Code:
Sub Test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Select Case True
            Case ws.Name Like "Remaining*", _
                ws.Name Like "No_Break*", _
                ws.Name Like "Listed_Instruments*", _
                ws.Name Like "Net_Zero_Difference*", _
                ws.Name Like "One_Dodge_Many_FO*", _
                ws.Name Like "Many_Dodge_One_FO*", _
                ws.Name Like "Materiality*"
                ws.Delete
        End Select
    Next ws
End Sub
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top