Case Statement with Wild Cards

[JackDanIce]

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:

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]

That is looking at the literal values. To use wildcards you need something like

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

 

[JackDanIce]

Thanks for the clarification Peter, makes sense

 

[JackDanIce]

But is there not a way to use CASE instead of lots of OR tests?

 

[VoG]

Sorry, I don't know a way of using wildcards in Select Case statements.

 

[JackDanIce]

No worries, see if anyone else posts something. It seemed like something suitable for CASE!
Thanks Peter,
Jack

 

[Andrew Poulsom]

You can do this:

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

 

[JackDanIce]

Thanks Andrew, will try that too