VBA to show/hide rows

[stephen.smith]

Hey Guys I am trying to show or hide rows in a spreadsheet based on the value in a cell. The range is cells B4:B532, if the cell in column B in any given row in the range has a value of OK i want the entire row to hide but if the cell has a value of ALERT i want the entire row to be seen. Can anyone help with code to do this.
Thanks in advance
Stephen

 

[SteveO59L]

Why not use a filter ?

 

[stephen.smith]

I have to run a macro on this page to update some references and change some values so if I can add some code in that will do this also then I would be happy to do this.
Thanks

 

[SteveO59L]

Something along the lines of

Dim c As Range, rng
Set rng = Range("b4:b532")
For Each c In rng
If c.Value = "Ok" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c

 

[stephen.smith]

I have tried that but it doesnt work
Any other ideas.
Thanks
Stephen

 

[SteveO59L]

In what way does it not work ?

Try

if c.value="OK"

 

[stephen.smith]

The macro runs but nothing gets hidden. The formula is in exactly as you posted but it doesnt work for me.

 

[AD_Taylor]

You could use:

Sub HideRows()

Dim c As Range, rng As Range
Dim checkVal As String

checkVal = "Ok"
Set rng = Range("B4:B532")

    For Each c In rng.Cells

    If c.Value = "Ok" Then
    c.EntireRow.Hidden = True
    Else
    c.EntireRow.Hidden = False
    End If

    Next c         

End Sub

 

[stephen.smith]

Hi Adam
Sorry but this doesnt work either, nothing gets hidden. Just so you know all cells in column B are referenced to cells in another worksheet which hold a value of either "ok" or "alert". will this make a difference?

 

[AD_Taylor]

Hmm... Try:

Sub HideRows()  
Dim c As Range, rng As Range 
Dim checkVal As String  

checkVal = "ok" 
Set rng = Range("B4:B532")      

For Each c In rng.Cells      

If c.Value = checkVal Then     
c.EntireRow.Hidden = True     
Else     
c.EntireRow.Hidden = False     
End If      
Next c           

End Sub

Now you can just change the variable checkVal to the relevant entry that you want to hide for. Its most likely because VBA code is case-sensitive so 'Ok' is different to 'ok'.