Auto hide rows

[Ada339]

Hello!

Is there a way to automatically hide a row when specific data is entered into a cell in that row?

At the moment i'm using the following:

Sub HideNO()
Dim cl As Range
For Each cl In Range("AI14:AI900")
If cl.Value = "No" Then
cl.EntireRow.Hidden = True
End If
Next
End Sub



But my supervisor wants it to be even simpler to "minimize the chances of human error"

Any help would be greatly appreciated!

Cheers

 

[Joe4]

If you want it to happen automatically when data is entered manually into a cell in that range, you can use an Event Procedure, specifically one that runs when cells are changed.
Here is code that will do that:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Intersect(Target, Range("AI14:AI900"))
    
    If Not myRange Is Nothing Then
        For Each cell In myRange
            If cell.Value = "No" Then cell.EntireRow.Hidden = True
        Next cell
    End If

End Sub

In order for this to run automatically, it must be placed in the proper sheet module. One easy way of ensuring that is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, and copy the code above into the VB Editor window.

 

[Ada339]

Hello again,

(I think) I'm having trouble with this. I copied and paste the script you provided into the visual basic editor, click save but nothing has happened. are there other fields i have to edit?

Thanks!

 

[Ada339]

never mind! it was my mistake!

(this is what my boss meant by 'human error'.... me. she was talking about me lol

Thank you!

 

[Joe4]

You are welcome.

 

[Ada339]

Hey Joe?

Can i insert multiple codes per sheet? i just tried and i had a 'Compile error' that read 'Ambiguous name detected: Worksheet_Change'.
It highlighted the top row of the code :Private Sub Worksheet_Change(ByVal Target As Range)

sorry to keep coming back to you, this is not my forte

Thanks!

 

[Joe4]

You can only have one Worksheet_Change event procedure per sheet, but you can putting many blocks of code within it. Just place you new block of code below the existing one (before the "End Sub" line).