Select Case with multiple "Like" conditions

Question

I am trying to reduce a large If statement by creating a smaller Case statement. I am not sure what I am doing wrong, but here is what I have so far.

It is similar to this post, but doesn't address the multiple situations and one result of my issue.

With tempWB.Worksheets(1)
    rwCnt = .cells(Rows.Count, 1).End(xlup).Row
    .Rows(rwCnt).Delete shift:=xlShiftUp
    rwCnt = rwCnt - 1

    For y = rwCnt to 2 step -1
        'Delete Non-Individuals
        Select Case .Cells(y, 1).Value2
            Case (.Cells(y, 1).Value2 Like ("* TRUST *" Or "* AND *" Or "* & *" Or "* OF *" Or _
            "* LLC*" Or "* REV TR *" Or "* LV TR *" Or "* BY *" Or "*'S *" Or "C/O*"))
                .Rows(y).Delete shift:=xlShiftUp
        End Select
    Next y

'        If .Cells(y, 1).Value2 Like "* TRUST *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* AND *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* & *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* OF *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* LLC*" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* REV TR *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* LV TR *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "* BY *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "*'S *" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        ElseIf .Cells(y, 1).Value2 Like "C/O*" Then
'            .Rows(y).Delete shift:=xlShiftUp
'        End If


End With

Below the Case statement is the If statement that works great, it just looks clunky and I think a Case statement would simplify things a bit. I'm just not 100% sure how to implement it. Thanks in advance.

See in that linked post that you need to use `Select Case True`. The result of a `Like` comparison is a `Boolean`. That said, you'd need to repeat the `.Cells(y, 1).Value2`, you can't string together the right side of `Like` like you're trying to. — BigBen, Jul 17 '19 at 15:37
@BigBen - So because the conditions use a `wildcard` and `like` I can't make it a lot shorter? — A Cohen, Jul 17 '19 at 15:39
It's because the `Like` comparison returns a `Boolean`. The wildcard has nothing to do with it. — BigBen, Jul 17 '19 at 15:40
@BigBen - But the wildcard won't work without a `Like`. So, the `case statement` would need to be just as long as the `If statement` — A Cohen, Jul 17 '19 at 15:48
It would be slightly shorter because you'd only have one instance of `.Rows(y).Delete shift:=xlShiftUp`. — BigBen, Jul 17 '19 at 15:49

score 2 · Accepted Answer · answered Jul 17 '19 at 15:56

You can't string together the right side of that Like comparison like you're attempting to.

Also, as noted in the linked post, you need to use Select Case True, because the result of a Like comparison is a Boolean.

The Select Case could then look something like this:

Select Case True
    Case .Cells(y, 1).Value2 Like "* TRUST *", _
         .Cells(y, 1).Value2 Like "* AND *", _
         .Cells(y, 1).Value2 Like "* & *", _ 
         '... and so on

        .Rows(y).Delete shift:=xlShiftUp
End Select

score 1 · Answer 2 · answered Jul 17 '19 at 15:54

Use Case True as was pointed out in the comments.

   Select Case True
        Case .Cells(y, 1).Value2 Like "* TRUST *" _
        Or .Cells(y, 1).Value2 Like "* AND *" _
        Or .Cells(y, 1).Value2 Like "* & *" _
        Or .Cells(y, 1).Value2 Like "* OF *" _
        Or .Cells(y, 1).Value2 Like "* LLC*" _
        Or .Cells(y, 1).Value2 Like "* REV TR *" _
        Or .Cells(y, 1).Value2 Like "* LV TR *" _
        Or .Cells(y, 1).Value2 Like "* BY *" _
        Or .Cells(y, 1).Value2 Like "*'S *" _
        Or .Cells(y, 1).Value2 Like "C/O*"
            .Rows(y).Delete shift:=xlShiftUp
    End Select

An alternative is to write a function to iterate over the conditions:

Function Likes(Value As Variant, ParamArray Conditions() As Variant)
    Dim Condition
    For Each Condition In Conditions
        If Value Like Condition Then
            Likes = True
            Exit Function
        End If
    Next
End Function

Usage

If Likes(.Cells(y, 1).Value2, "* TRUST *", "* AND *", "* & *", "* OF *", "* LLC*", "* REV TR *", "* LV TR *", "* BY *", "*'S *", "C/O*") Then
    .Rows(y).Delete shift:=xlShiftUp
End If

Select Case with multiple "Like" conditions

2 Answers2

Usage