using vba to hide certain rows based on their value

[Talisman of Death]

hi.

What I want to achieve: Look at column B range. For each cell it could be 0 or a number. If 0, I want to hide that entire row. If not 0 I want to show that row. (Later I will also want to show the rows relating to the numbers in B.)

Sub HidingMatchedRowsWithRangeSelection()

'To select a range in Column B
Dim MyLastRow As Integer
Dim MySelection A Range


Cells(3, 2).Select
ActiveCell.End(xlDown).Select
MyLastRow = ActiveCell.Row

MySelection = Range(Cells(3, 2), Cells(MyLastRow, 2)).Select


'Now go through each cell in the range and hide row if cell value is 0
For Each cl In MySelection
If cl.Value = 0 Then
cl.Row.EntireRow.Hidden = True
End If
Next cl

I get a runtime error 13 message. The first part seems to work. It is the For Each bit that appears to be wrong.

thanks

 

[onlyadrafter]

Hello,

try this

Sub HIDE_COL_B_ZEROS()
    For MY_ROWS = 3 To ActiveSheet.UsedRange.Rows.Count
        If Range("B" & MY_ROWS).Value = 0 Then
            Rows(MY_ROWS).EntireRow.Hidden = True
        Else
            Rows(MY_ROWS).EntireRow.Hidden = False
        End If
    Next MY_ROWS
End Sub

and to unhide all rows

Sub SHOW_ALL_B()
    ActiveSheet.Rows.Hidden = False
End Sub

 

[Talisman of Death]

thanks for the post, I will try this (mine wasn't very efficient)