excelvba

How to hide rows when cell values equal to zero in Excel VBA


I am trying to hide rows when the cell values equal to 0 with some Excel VBA codes. However, the macro is not always working properly. It will hide rows even when the cell values are bigger than zero.

The scenario would be the person will input different customer IDs into a cell and hit the "Refresh" button that includes the following code. The cells will be hidden if the purchase amount shows 0. Can anyone please let me know where I did wrong?

Thank you

Sub HideRows()


    Rows.EntireRow.Hidden = False
    
    ThisWorkbook.Sheets("Template").Activate
    
        If ThisWorkbook.Sheets("Template").Range("E32,E39,E40,E50,E51").Value = 0 Then
       
            Range("E32,E39,E40,E50,E51").EntireRow.Hidden = True
        Else
             Range("E32,E39,E40,E50,E51").EntireRow.Hidden = False
        End If


    
End Sub

Solution

  • Range("E32,E39,E40,E50,E51").Value = 0 dosn't work as what you expected.

    Sub HideRows()
        Dim c As Range
        With ThisWorkbook.Sheets("Template")
            .Rows.EntireRow.Hidden = False
            For Each c In .Range("E32,E39,E40,E50,E51")
                If Not VBA.IsError(c.Value) Then
                    If c.Value = 0 Then
                        c.EntireRow.Hidden = True
                    End If
                End If
            Next
        End With
    End Sub