CODEWITHSUNDEEP
vbaexcel
maniA
maniA

Reputation: 1457

combine Like (String Pattern) with Case in VBA

I want to combine Like with Case in a VBA code. My current code is able to remove all sheets whose name does not match the names in range A:

Dim MyCell As Range, MyRange As Range
Dim wbook As Workbook, xWs As Worksheet
Dim DeleteSheetFlag As Boolean

Set MyRange = Sheets("Summary").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wbook = ActiveWorkbook

For Each xWs In wbook.Worksheets
    DeleteSheetFlag = True

    For Each MyCell In MyRange
        Select Case xWs.Name

            Case MyCell.Value, "Summary"
                DeleteSheetFlag = False
                Exit For

        End Select

    Next MyCell

    If DeleteSheetFlag Then
        xWs.Delete
    End If

Next xWs

Application.DisplayAlerts = True
Application.ScreenUpdating = True

I want to change it in that way, that it removes all sheets whose name are not similar to the names in Range A. If I would be able to get something like 

 Case **Like** MyCell.Value, "Summary"
                DeleteSheetFlag = False

the problem would be solved. I mean with similar the pattern match which we know from Regular Expressions. In my case I have for example two sheets with the names : Etat1 and Etat1(2) and I want to put in Range A11 the string Eta in order to keep both.

Upvotes: 3

Views: 2508

Answers (2)

stealey
stealey

Reputation: 1

Case IIf(xWs.Name Like "*" & MyCell.Value & "*", xWs.Name, ""), "Summary"

Upvotes: 0

Jacques Lalancette
Jacques Lalancette

Reputation: 141

To force this you would need to use a workaround like this:

Select Case True
     Case MyCell.Value Like "*Summary*"
           Debug.Print "!"
           'DeleteSheetFlag = False
           '...
     Case MyCell.Value Like "*OtherCase*"
           DeleteSheetFlag = False
           '...
End Select

Upvotes: 3

Related Questions