Ada339

New Member
Joined
Dec 10, 2017
Messages
20
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

New Member
Joined
Dec 10, 2017
Messages
20
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

New Member
Joined
Dec 10, 2017
Messages
20
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
 

Ada339

New Member
Joined
Dec 10, 2017
Messages
20
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top