Hide corresponding rows when cell value = 0

0
votes

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.

Help much appreciated.

2
excelvba

2 Answers

1
votes

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

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_Calculate event or change the value to a non zero value in the connected cell (provided the connected cell in not in the hidden row).

This will hide the row when the value in Col E changes to 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
0
votes

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_Click event or add next method to necessary Sheet in Microsoft Excel Objects

Private Sub Worksheet_Change()
    Module1.HideRows
End Sub

This method will invoke HideRow method when some cell changed.