如果列中的单元格包含某些文本则隐藏行或使用4个条件自动过滤单个字段

Tanner 发表于 Dev

皮匠

我的工作表的A到M列包含一个包含所有行和列的表。如果在E列中某个单元格包含字符串“驱动器”，“不活动”或“暂停”，则我希望该行被隐藏。如果在列E，细胞就不能包含字符串“UF_”，那么我希望它被隐藏。

我尝试了几件事，并在许多地方进行了研究。这是我尝试过的一些代码：

尝试1（将Wayyyy设置为long）：

With ActiveSheet
loopct = 2

While loopct < count1
    DoEvents
    Application.StatusBar = "Making Table " & loopct
    txtrmv1 = "Drive"
    txtrmv2 = "Inactivity"
    txtrmv3 = "Halt"
    txtkp = "UF_"

    celltxt = .Range("E" & loopct).Value

    If InStr(1, celltxt, txtrmv1, vbTextCompare) Then
        .Range("E" & loopct).EntireRow.Hidden = True
    End If
    If InStr(1, celltxt, txtrmv2, vbTextCompare) Then
        .Range("E" & loopct).EntireRow.Hidden = True
    End If
    If InStr(1, celltxt, txtrmv3, vbTextCompare) Then
        .Range("E" & loopct).EntireRow.Hidden = True
    End If
    If InStr(1, celltxt, txtkp, vbTextCompare) Then
        .Range("E" & loopct).EntireRow.Hidden = False
    Else
        .Range("E" & loopct).EntireRow.Hidden = True
    End If

    loopct = loopct + 1
Wend

End With

尝试2（运行但什么也不做）：

Private Sub HideDrive(ByVal count1 As Long)
    Dim ws As Worksheet
    Dim rng As Range, aCell As Range, bCell As Range

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    With ws
        Set rng = .Range("E2:E" & CStr(count1))

        Set aCell = rng.Find(What:="Drive", LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=True, SearchFormat:=False)

        If Not aCell Is Nothing Then
            Set bCell = aCell
            Do
                aCell.EntireRow.Hidden = True
                Set aCell = rng.FindNext(After:=aCell)

            Loop While aCell Is Nothing And aCell.Address <> bCell
        End If
    End With
End Sub

这是我只有一个条件要检查时使用的东西（显然我的情况已经改变）：

ActiveWorkbook.ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=5, Criteria1:= "=*UF_*"

我该怎么做才能实现自己想要的？我无法使自动筛选器与两个以上的条件一起工作。请告诉我！

皮匠

我无法调试并运行给出的其他答案，所以我继续工作并自己解决了问题。

我没有试图隐藏我不想全部在一起的每个单词，而是分别隐藏了它们，然后每次都调用隐藏行删除功能。

        ActiveSheet.ListObjects.Add(xlSrcRange, Range(DataArea1), , xlYes).Name = _
            tblnm

        'insert if statement here to change filters based upon area
        ActiveWorkbook.ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=5, Criteria1:="=*UF_*"

        Call RhidRow2(count4)

        ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=5, Criteria1:="<>*Drive*"

        Call RhidRow2(count4)

        ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=5, Criteria1:="<>*Inactivity*"

        Call RhidRow2(count4)

        ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=5, Criteria1:="<>*Halt*"

        Call RhidRow2(count4)

        ActiveSheet.ListObjects(tblnm).Range.AutoFilter Field:=8, Criteria1:="<>#VALUE!"

        Call RhidRow2(count4)

这是隐藏的行删除器：

Private Sub RhidRow2(ByVal count4 As Long)
    Dim count1 As Long 'counters to be used
    Dim ws As Worksheet
    Dim rngVis As Range
    Dim rngDel As Range
    Set ws = ActiveSheet

    On Error Resume Next
    Set rngVis = ws.Range("A2:A" & count4).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rngVis Is Nothing Then
        ws.Range("Z1").Value = 1
    Else

        For count1 = count4 To 2 Step -1
            If ws.Rows(count1).Hidden = True Then
                If rngDel Is Nothing Then
                    Set rngDel = ws.Rows(count1)
                Else
                    Set rngDel = Union(rngDel, ws.Rows(count1))
                End If
            End If
        Next count1

    If Not rngDel Is Nothing Then
        Application.DisplayAlerts = False
        Intersect(rngDel, rngDel.ListObject.DataBodyRange).Delete 'if row is hidden, delete
        Application.DisplayAlerts = True
    End If

    End If
End Sub

这比我尝试过或建议的其他任何工具都更好，更快。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。