VBA code to auto-hide row based upon entries in a range of cells in this row

[virtuosok]

Hi,
I would really appreciate help with VBA code to hide a given row based upon entries in columns F-N in the same row.
Total number of rows to apply this rule to is 20 (i.e. rows 10-29)
More specifically, cells in columns F-N all have a SUMIF type of formula which returns either zero or a different number (0.5...200).
I want to hide row 10, if all formulas in 10F:10N return 0
Likewise, hide row 11, if all formulas in 11F:11N return 0...
and so on.
Is it possible?

 

[Marc L]

​

Hi, yes with an advanced filter for example …​

 

[virtuosok]

I would ideally need a code please... tried to work with this one but in vain

Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
On Error Resume Next
For Each c In Range("F10:N29" & LastRow)
   If c.Value = 0 Then
        c.EntireRow.Hidden = True
    ElseIf c.Value > 0 Then
        c.EntireRow.Hidden = False
    End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub

Just to confirm, if there is one or several zeros in F:N range (and one or more cells with >0 values), I would like to have the row in question unhidden. It's only when all cells, from F to N, in a given row, are zeros, I want the row to be hidden.
A workable version (if there is one) will need to be appended to the existing sub, I would also appreciate some guidance how to do it. Working sub is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
    Select Case Target(1).Address
           Case "$A$2":         [A5,A8].ClearContents
           Case "$A$5", "$A$8": [A2].ClearContents
    End Select
        Application.EnableEvents = True
End Sub

 

[virtuosok]

Ok I have added a helper column with SUM type of formulas; they return either 0.0 or a value higher than that.
Why is the below code not working please? I want it to start from row 15 onwards, and when a value in, say, C15 is 0.0 then the row should be hidden... all the way to row 200.

Sub HideRows()
StartRow = 15
EndRow = 200
ColNum = 3
For i = StartRow To EndRow
If Cells(i, ColNum).Value = "0.0" Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i
End Sub

 

[virtuosok]

...because I should have entered "0", not "0.0", that's why
Now, while it worked first time around and returned 7 rows, hiding everything else as expected, it doesn't not necessarily work moving forward as the content in my spreadsheet is changing dynamically. So when the content changes, the total # of rows remains 7, while in some cases it should be 5 and in others it should be 12. Thus some rows continue to be hidden even if not blank anymore. Obviously the code above is suboptimal... please help re-wording it.

 

[Marc L]

​

With an attachment on a files hosting website (dropbox, easyupload, …) it should be easier to help.​

 

[virtuosok]

Ok here's the link to the blinded file.
Dropbox
On tab Sheet 1, I would need the rows 15 onwards to auto-hide whenever they have 0.0 in cell C. In the future, there will be up to 200 rows of that sort.
Content gets updated by toggling names in cell A2, e.g. Sarah, Jennifer, Kim

 

[Marc L]

According to your attachment a filter demonstration :​

Sub Demo1()
    Sheet5.[A14].CurrentRegion.Columns(3).AutoFilter 1, ">0"
End Sub

 

[Marc L]

Why is the below code not working please?

When obviously Value is not a text : If Cells(i, ColNum).Value = 0 Then …​

 

[virtuosok]

Thanks *Marc L!
The filter solution works once, but if the name in cell A2 is updated and rows 15 onwards refresh with new data, filter is not working properly...