Hide a Row depending on cells values

Question

I want to Hide a Row if the values in multiple cells are equal to 0. I have tried this VBA code, it “works” but at the end the rows with 0 are not being hidden.

Sub Rehien_ausblenden()
    Dim i As Integer
    Application.ScreenUpdating = False
    Worksheets("Daten").Activate
    For i = 12 To 45
        If Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 _
        And Cells(i, 9).Value = 0 And Cells(i, 10).Value Then
          Rows(i).EntireRow.Hidden = True
        End If
    Next
        MsgBox ("Completed")
        Application.ScreenUpdating = True
 
End Sub

Pᴇʜ · Accepted Answer · 2020-09-21T09:39:17.043

And Cells(i, 10).Value Then is probably missing = 0 ? Please check if And Cells(i, 10).Value = 0 Then works.

Further I recommend the following changes:

Avoid using .Activate and use a With statement instead (You might benefit from reading How to avoid using Select in Excel VBA).
Use Long instead of Integer: There is no benefit in using Integer in VBA (Why Use Integer Instead of Long?) but you easily run into errors using Integer for row counting because Excel has more rows than Integer can handle.

Note that your code only hides rows but never unhides anything. So if you run this multiple times any hidden row will always stay hidden, even if the if statement is not true anymore.

Sub Rehien_ausblenden()
    Application.ScreenUpdating = False

    With ThisWorkbook.Worksheets("Daten")
        Dim i As Long
        For i = 12 To 45
            If .Cells(i, 7).Value = 0 And .Cells(i, 8).Value = 0 _
               And .Cells(i, 9).Value = 0 And .Cells(i, 10).Value = 0 Then
                .Rows(i).EntireRow.Hidden = True
            End If
        Next i
    End With

    MsgBox "Completed"
    Application.ScreenUpdating = True
End Sub

score 0 · Answer 2 · answered Sep 21 '20 at 09:34

As mention by Ren, you indeed need to correct your code as follows:

        If Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 _
        And Cells(i, 9).Value = 0 And Cells(i, 10).Value = 0 Then

(add = 0 at cell in column 10)

In addition, I'd like to inform you that not filling in a value in a cell also results in the cell value being zero: in my case I had only filled in value 0 in row 12, all the rest was blank but still all rows, from 12 up to 45, are hidden.

Hide a Row depending on cells values

2 Answers2