vba 当单元格值 = 0 时隐藏相应的行

Hide corresponding rows when cell value = 0

提问by user1444404

I am trying to automatically hide/unhide corresponding rows when cell value in column E equals 0 (zero). There are formulas in these cells, and these formulas returns zero when a cell in another changes. Upon that change i would like the code to perform its hide/unhide magic.

当 E 列中的单元格值等于 0（零）时，我试图自动隐藏/取消隐藏相应的行。这些单元格中有公式，当另一个单元格发生变化时，这些公式返回零。在进行更改后，我希望代码执行其隐藏/取消隐藏魔术。

Help much appreciated.

非常感谢帮助。

回答by Siddharth Rout

Here is a faster method using AutoFilter. You can call this code directly or use it in Worksheet_Calculateevent. I am assuming the Cell E1has Headers.

这是使用 AutoFilter 的更快方法。您可以直接调用此代码或在Worksheet_Calculate事件中使用它。我假设单元格E1有标题。

IN A BUTTON

一个按钮

Option Explicit

Sub Sample()
    Dim rRange  As Range, RngToHide As Range
    Dim lRow As Long

    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False

    With Sheets("Sheet1")
        lRow = .Range("E" & Rows.Count).End(xlUp).Row
        Set rRange = .Range("E1:E" & lRow)

        With rRange
          .AutoFilter Field:=1, Criteria1:="0"
          Set RngToHide = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
    End With

    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False

    If Not RngToHide Is Nothing Then RngToHide.Hidden = True
End Sub

WORKSHEET CALCULATE EVENT - Not Recommended

工作表计算事件 - 不推荐

I do not recommend calling this code automatically as this will not let you unhide the rows in case you want to change something in the hidden rows. To unhide the rows, you will have to either comment out the entire code in the Worksheet_Calculateevent or change the value to a non zero value in the connected cell (provided the connected cell in not in the hidden row).

我不建议自动调用此代码，因为这不会让您取消隐藏行，以防您想更改隐藏行中的某些内容。要取消隐藏行，您必须注释掉Worksheet_Calculate事件中的整个代码，或者将连接单元格中的值更改为非零值（前提是连接单元格不在隐藏行中）。

This will hide the row when the value in Col E changes to 0

当 Col E 中的值更改为时，这将隐藏该行 0

Option Explicit

Private Sub Worksheet_Calculate()
    Dim rRange  As Range, RngToHide As Range
    Dim lRow As Long

    On Error GoTo Whoa

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False

    With Sheets("Sheet1")
        lRow = .Range("E" & Rows.Count).End(xlUp).Row
        Set rRange = .Range("E1:E" & lRow)

        With rRange
          .AutoFilter Field:=1, Criteria1:="0"
          Set RngToHide = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
    End With

    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False

    If Not RngToHide Is Nothing Then RngToHide.Hidden = True

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

回答by KopBuH

Use this mhetod:

使用这个方法：

Sub HideRows()
Dim i As Integer
i = 1
Do While Not Cells(i, 5) = ""
    If Cells(i, 5).Value = 0 Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
    ElseIf Cells(i, 5).Value <> 0 And Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = False
    End If
i = i + 1
Loop
End Sub

You can add a button and invoke this method by Button_Clickevent or add next method to necessary Sheetin Microsoft Excel Objects

您可以添加一个按钮，通过调用此方法Button_Click事件或下一个方法添加到必要的表中的Microsoft Excel对象

Private Sub Worksheet_Change()
    Module1.HideRows
End Sub 

This method will invoke HideRowmethod when some cell changed.

HideRow当某些单元格更改时，此方法将调用方法。