如果一列中的单元格包含某个字符串，如何隐藏行

I have a worksheet that is set out like a calendar - Every column is a working day until the end of the month, Column A is the name of a task, each row is a specific task (8 tasks), and then the next month underneath using the same tasks.

我有一个像日历一样的工作表 - 每列是一个工作日，直到月底，A列是任务的名称，每行是一个特定的任务（8个任务），然后是下个月在下面使用相同的任务。

Currently we are using a toggle button for each task with the following code to hide each row (Other rows removed to save space):

目前我们使用每个任务的切换按钮，使用以下代码隐藏每一行（为了节省空间而删除其他行）：

Private Sub ToggleButton13_Click()
If ToggleButton13.Value = True Then

        Rows(22).EntireRow.Hidden = True

    Else

        Rows(22).EntireRow.Hidden = False

    End If
End Sub

This was fine, but this calendar now keeps growing with additional tasks requiring us to go into each togglebutton and every row to change it accommodating the new task. As the calendar doesn't belong to me, I've got no experience with any form of coding so found this previously, the actual user doesn't want to have to change this manually (I can understand why, it can be time consuming). I'm wondering if we can use the 'IF' function with the togglebutton?

这很好，但是这个日历现在不断增长，其他任务要求我们进入每个togglebutton和每一行来改变它以适应新的任务。由于日历不属于我，我没有任何形式的编码经验，所以以前发现，实际用户不想手动更改（我可以理解为什么，这可能是耗时的）。我想知道我们是否可以在togglebutton中使用'IF'功能？

So effectively:

如此有效：

1. Everything is Visible
2. 一切都是可见的

3. Press ToggleButton13

   按ToggleButton13

   IF ColumnA cell contains 'Admin' then hide the row ELSE If ColumnA cell doesn't contain 'Admin' then show the row

   如果ColumnA单元格包含“Admin”，则隐藏行ELSE如果ColumnA单元格不包含“Admin”，则显示该行

3 个解决方案

#1

Two ways I can think of:

我能想到的两种方式：

Cycle through each row and set the hidden property.
As the code is behind the ToggleButton we don't need to specify the sheet name. The code will run on the currently active sheet which is the correct sheet because you pressed the ToggleButton on it.

遍历每一行并设置隐藏属性。由于代码在ToggleButton后面，我们不需要指定工作表名称。代码将在当前活动的工作表上运行，这是正确的工作表，因为您按下了ToggleButton。

As ToggleButton returns either TRUE or FALSE we can just set the hidden property to that value.

由于ToggleButton返回TRUE或FALSE，我们只需将hidden属性设置为该值即可。

Private Sub ToggleButton1_Click()
    Dim rDataRange As Range
    Dim rCell As Range

    'Set rDataRange = Range("A2", Cells(Rows.Count, 1).End(xlUp))
    'Take into account hidden rows:  
    Set rDataRange = Range("A2", HiddenLastCell(ActiveSheet))

    For Each rCell In rDataRange
        If rCell = "Admin" Then
            Rows(rCell.Row).EntireRow.Hidden = ToggleButton1
        End If
    Next rCell
End Sub 

Setting the range in rDataRange uses this function to find the last cell even if it's hidden:

在rDataRange中设置范围使用此函数来查找最后一个单元格，即使它被隐藏：

Public Function HiddenLastCell(wrkSht As Worksheet) As Range

    Dim rLastCell As Range
    Dim bHasHiddenData As Boolean
    Dim rSearch As Range
    Dim lLastCol As Long, lLastRow As Long
    Dim lRow As Long

    With wrkSht
        Set rLastCell = .Columns(1).Find("*", , , , xlByColumns, xlPrevious)

        If Not rLastCell Is Nothing Then
            bHasHiddenData = rLastCell.Row <> .UsedRange.Rows.Count
        Else
            bHasHiddenData = .UsedRange.Rows.Count > 1
        End If

        If bHasHiddenData Then
            Set rSearch = .Range(.Cells(1, 1), .Cells(.UsedRange.Row + .UsedRange.Rows.Count - 1, 1))
            For lRow = rSearch.Rows.Count + 1 To 2 Step -1
                If .Cells(lRow, 1) = vbNullString And .Cells(lRow - 1, 1) <> vbNullString Then
                    Set HiddenLastCell = .Cells(lRow, 1)
                End If
            Next lRow
        Else
            On Error Resume Next
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
            If lLastCol = 0 Then lLastCol = 1
            If lLastRow = 0 Then lLastRow = 1
            Set HiddenLastCell = wrkSht.Cells(lLastRow, lLastCol)
            On Error GoTo 0
        End If
    End With

End Function

---

Use AutoFilter to hide the rows.
Add this code to the button:

使用AutoFilter隐藏行。将此代码添加到按钮：

Private Sub ToggleButton1_Click()
    FilterAdmin ToggleButton1.Value
End Sub

This code will apply or remove the filter:

此代码将应用或删除过滤器：

Sub FilterAdmin(ToggleOn As Boolean)

    Dim rDataRange As Range
    Dim rLastCell As Range

    With ThisWorkbook

        If ToggleOn Then
            Set rLastCell = LastCell(.Worksheets("Sheet1"))
            With .Worksheets("Sheet1")
                'Get reference to data range (A1 to last used cell).
                'Or you can manually set the range.
                Set rDataRange = .Range(.Cells(1, 1), rLastCell)

                'If auto-filter isn't turned on then turn it on.
                If Not .AutoFilterMode Then rDataRange.AutoFilter

                'Remove any applied filters.
                If .FilterMode Then .ShowAllData

                rDataRange.AutoFilter Field:=1, Criteria1:="<>Admin", Operator:=xlAnd
            End With
        Else
            If .Worksheets("Sheet1").FilterMode Then .Worksheets("Sheet1").ShowAllData
        End If
    End With

End Sub  

This function will return the last cell that contains data and is used in the above procedure:

此函数将返回包含数据的最后一个单元格，并在上述过程中使用：

Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function  

You could change the sheet references in the code to use ActiveSheet rather than Sheet1 (again, the correct sheet will be active when you press the toggle button).

您可以更改代码中的工作表引用以使用ActiveSheet而不是Sheet1（再次，当您按下切换按钮时，正确的工作表将处于活动状态）。

#2

Assumption: Entries in column A are continuous... it will come handy as your sheet keeps growing, so just need to follow a format.

假设：A栏中的条目是连续的...当您的工作表不断增长时它会很方便，所以只需要遵循一种格式。

     Private Sub ToggleButton13_Click()

        Dim indexCol, indexRow as Integer
        dim myKey as String

        myKey = "Admin"


        indexCol = 1 ' For Column A
    indexRow = 2 'row index from where the first entry starts in the Column


    ' loop will traves each row utill columnA entry correspondig to that row is not empty
    Do While IsEmpty(ActiveSheet.Cells(indexRow, indexCol)) = False 

    If ActiveSheet.Cells(indexRow, indexCol).Value = myKey Then

            Rows(indexRow).EntireRow.Hidden = True

        Else

            Rows(indexRow).EntireRow.Hidden = False

        End If

        indexRow = indexRow + 1

    Loop    

    End Sub

#3

Try a general loop, similar to what Ron Rosenfield suggested in the comments. Will comment through it since you said you're inexperienced with coding:

尝试一般循环，类似于Ron Rosenfield在评论中建议的内容。因为您说您对编码缺乏经验，所以会通过它发表评论：

Dim i, LR as Integer 'i will be variable for row in the loop, LR as variable for last row
LR = Cells(Rows.Count,1).End(xlUp).Row 'Determines the last row based on contiguous cells in column 1, aka column A
For i = 2 to LR 'Starting at Row 2 with the assumption that you have headers
    If Cells(i,1).Value="Admin" Then 'Cells(row,column) here is column 1, aka column A.  
        Rows(i).EntireRow.Hidden=True 'Hides row if true
    Else
        Rows(i).EntireRow.Hidden=False 'Ensure row is not hidden if false.
    End If
Next i

This would be inside your button click macro.

这将在您的按钮单击宏内。