Macro Help

[Dan in Lowell]

Hi,

I am very new to VBA and would like to create a macro that will read down column B for a value of 'Closed" and when it finds this value will hide the entire row and then continue reading down the column.

Can anyone help?

Thanks in Advance

 

[Datsmart]

Sub HideSomeRows()
    Dim HideRange As Range, c As Range

' First Unhide any hidden rows
    Columns(2).EntireRow.Hidden = False
    
    Set HideRange = Range("B1:B" & _
    Range("B65536").End(xlUp).Row)
    
        For Each c In HideRange
            If c.Value = "Closed" Then c.EntireRow.Hidden = True
        Next c

End Sub

 

[HalfAce]

This can also be done without looping by using autofilter.
(Much quicker if you have many rows.)

Sub Demo()
Dim LstRw As Long, bRng As Range
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Columns("B:B").AutoFilter Field:=1, Criteria1:="Closed"
Set bRng = Range(Cells(2, "B"), Cells(LstRw, "B")).SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
bRng.EntireRow.Hidden = True
End Sub

Note - there is a limit of (I think) about 8192 cells that can contain "Closed" before you run
into problems using the SpecialCells method.