excel - VBA:如何对值列表使用like运算符？

Question

这是我的代码的一部分。有什么办法可以让它变得简单吗？谢谢。

For i = 2 To ws.Range("E1").CurrentRegion.Rows.Count

If ws.Cells(i, 4).Value Like ("*SSI*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Settlement instruction*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*delivery Instruction*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Request form*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.cells(i, 4).Value Like ("*Sales to onboarding*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Application*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Doc Check list*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime to Credit*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime to Legal*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime_Legal*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Prime_Credit*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*LEXIS*") Then ws.Cells(i, 4).EntireRow.Delete
If ws.Cells(i, 4).Value Like ("*Withdrawal Request*") Then ws.Cells(i, 4).EntireRow.Delete

Next i

Answer 1

有很多方法可以做到这一点，但这里是一种:

首先，删除行时，始终从范围的底部开始向上移动 - 这可以防止删除时发生行跳过。

我通过使用逗号分隔文本来创建一个数组。如果您的数据可能包含逗号，您需要更改它。

Dim tmpAr As Variant
Dim test As Variant

Set ws = ActiveSheet
tmpAr = Split("SSI,Settlement instruction,delivery Instruction,Request form,Sales to onboarding,Application,Doc Check list,Prime to Credit,Prime to Legal,Prime_Legal,Prime_Credit,LEXIS,Withdrawal Request", ",")
For i = ws.Range("E1").CurrentRegion.Rows.Count To 2 Step -1
    For Each test In tmpAr
        If ws.Cells(i, 4).Value Like "*" & test & "*" Then
            ws.Cells(i, 4).EntireRow.Delete
            Exit For
        End If
    Next
Next i