Hiding Rows with For Loop Over Range

March 16, 2022

I have some data that looks like the following (new user so can’t directly embed):

I’m aiming to hide any row that shows "#Missing" or 0 across all month columns. So from the attached picture, the first three rows would never be hidden, but as of right now, the 4th row would be hidden as there are only "#Missing" and 0 values.

I’ve done a good bit of perusing through older posts and found a few similar questions, but nothing that quite solves my niche issue (it seems niche). That’s all to say please forgive me if this has been solved elsewhere, I just couldn’t find it.

Here is my starting point, which may be wildly off-base. I feel like this should be a relatively easy solution, but not totally sure:

Sub RowHider()

Dim c As Range

For Each c In Range("I12:T10000")
    If c.Value = "#Missing" Or 0 Then
    c.EntireRow.Hidden = True
    End If
Exit For
Next c

End Sub

>Solution :

Perhaps something like this, using WorksheetFunction.CountIfs:

Sub RowHider()

Dim c As Range
Dim rngToHide As Range

For Each c in Range("I12:T10000").Rows
    If WorksheetFunction.CountIfs(c,"<>0",c,"<>#Missing") = 0
        If rngToHide Is Nothing Then
            Set rngToHide = c
        Else
            Set rngToHide = Union(rngToHide, c)
        End If
    End If
Next

If Not rngToHide Is Nothing Then
    rngToHide.EntireRow.Hidden = True
End If

Note that this will not hide rows that are entirely blank, but can be adjusted to if needed.