Hiding cells in columns when the value is zero

Question

Edit: I should clarify what I was trying to do. I have a list of values in column D through H and sometimes I may not have any values in the top most rows (they then equal 0, usually 40 rows or so). I want to make it so I don't have to scroll down to the rows that do have values not equaling zero. So I thought it would be easiest to hide the rows that have values equaling zero. But I have values in column A that I don't want to hide. I didn't realize I couldn't hide rows in a specific column without hiding the whole row. I need to rethink how I want to do this.

Original post: I am new to VBA, please bear with me. I have been copy and pasting different snippets of code, trying to get something to work.

I want to loop through all cells in column D through H and have the cells that equal zero to hide themselves. I plan on reusing this sheet so I want the cells to unhide themselves when the value is above zero again.

Here is my code:

Private Sub Hide_Cells_If_zero()`enter code here`

Dim targetRange As Range, po As Long, i As Long
Set targetRange = Columns("D:H")
po = targetRange.Count

With targetRange
For i = 1 To po
    If .Cells(i, 1).Value = 0 Then
    .Rows(i).Hidden = True
    End If
Next
End With

End Sub

I don't see your goal: Do you want to hide columns or rows? You can't hide individual cells. For i = 1 to po will count to many: You use it as a row index but po will be the number of cells in D:H, so 5 × number of rows. — z32a7ul
I want to hide the rows in those columns that have values that equal 0 — LibertyWolf
I don't get an error, its just not hiding all the rows in the columns that I laid out. — LibertyWolf

VBasic2008 VBasic2008 · Accepted Answer · 2020-12-05T15:58:38

Hide Rows With Criteria

This will hide each row where the value of any cell in columns D:H evaluates to 0.

The Code

Option Explicit

Private Sub Hide_Cells_If_zero()

    Dim sRng As Range
    Set sRng = Columns("D:H")

    Dim tRng As Range
    Dim rRng As Range
    Dim cel As Range

    For Each rRng In sRng.Rows
        For Each cel In rRng.Cells
            If cel.Value = 0 Then
                If Not tRng Is Nothing Then
                    Set tRng = Union(tRng, cel)
                Else
                    Set tRng = cel
                End If
                Exit For
            End If
        Next cel
    Next rRng

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

End Sub

Hiding cells in columns when the value is zero

1 Answers

Hide Rows With Criteria