excel - 在 Excel 中查找多个文本

Question

我在 excel 中有大量数据，我想删除包含我的“标签”的行。查找选项只允许我搜索 1 个单词，而我有数百个单词。它可能是一个宏或一个参数。我尝试了以下参数，但只找到了 1。

=IF(OR(ISNUMBER(SEARCH({"word1","word2","word3"},A1,B1)))"YES","NO")

请有人帮助我。

Answer 1

前



要运行的宏

Option Explicit

Sub RemoveRowsBasedOnArrayCondition()
    Dim searchTerms As Variant
    searchTerms = Array("tag1", "tag2", "tag3")

    ReDim rowsToDelete(0) As String

    Dim allRange As Range
    Set allRange = ActiveSheet.UsedRange

    Dim cell As Range, word As Variant
    For Each cell In allRange
        For Each word In searchTerms
            If InStr(1, cell, word, vbTextCompare) Then
                rowsToDelete(UBound(rowsToDelete)) = CStr(cell.Row)
                ReDim Preserve rowsToDelete(UBound(rowsToDelete) + 1)
            End If
        Next word
    Next cell
    ReDim Preserve rowsToDelete(UBound(rowsToDelete) - 1)
    RemoveDuplicate rowsToDelete
    Dim v As Long
    For v = UBound(rowsToDelete) To LBound(rowsToDelete) Step -1
        Rows(rowsToDelete(v)).Delete
    Next
End Sub


Sub RemoveDuplicate(ByRef StringArray() As String)
    Dim lowBound$, UpBound&, A&, B&, cur&, tempArray() As String
    If (Not StringArray) = True Then Exit Sub
    lowBound = LBound(StringArray): UpBound = UBound(StringArray)
    ReDim tempArray(lowBound To UpBound)
    cur = lowBound: tempArray(cur) = StringArray(lowBound)
    For A = lowBound + 1 To UpBound
        For B = lowBound To cur
            If LenB(tempArray(B)) = LenB(StringArray(A)) Then
                If InStrB(1, StringArray(A), tempArray(B), vbBinaryCompare) = 1 Then Exit For
            End If
        Next B
        If B > cur Then cur = B: tempArray(cur) = StringArray(A)
    Next A
    ReDim Preserve tempArray(lowBound To cur): StringArray = tempArray
End Sub

之后