VBA code to automatically hide rows

[smathu3]

I want to find a way to automatically hide rows in Excel when any particular cell is 0 within a specified column. In the example below, after running the macro, the column on top would collapse and then it would look like the column on the bottom (since the cells with 0 would have their rows be automatically hidden).

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]400[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]300[/TD]
[/TR]
[TR]
[TD]150[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]400[/TD]
[/TR]
[TR]
[TD]300[/TD]
[/TR]
[TR]
[TD]150[/TD]
[/TR]
[TR]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
This is one way of many :

Option Explicit


Sub HidebyVal()
Dim i As Integer
Application.ScreenUpdating = False
    For i = 2 To 300
        If Cells(i, 4).Value = "0" Then
            Cells(i, 4).EntireRow.Hidden = True
        End If
       
    Next
Application.ScreenUpdating = True
End Sub


Sub UnHide()
Dim i As Integer
Application.ScreenUpdating = False


'MsgBox "Unhiding"
    For i = 2 To 300
        If Cells(i, 4).Value = "0" Then
            Cells(i, 4).EntireRow.Hidden = False
        End If
    Next i
Application.ScreenUpdating = True


End Sub

 

[SpillerBD]

There are a few subtle differences between Hidden and Filtered.
Logit gave what you asked for, but I loath hidden rows because of the way they are treated with SUBTOTAL, AGGREGATE and other functions. Also, with a Filtered set, I can Copy/Paste without worrying about the Visible Cells Only.