I have created a checklist and would like for the rows to hide/unhide based off a cell's value. Do I need to some how embed a value for the rows I want hidden/unhidden or do I need to create a new sheet with the values and then link them to the main sheet? For example if the value in D4 is $10,000 I want to auto hide rows 62 through 75. I would need certain rows hidden or unhidden base on a range in the D4 value ($0 - $10,000,000).
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Hiding rows based on cell value
- Thread starter Newbie415
- Start date
- Joined
- Feb 28, 2002
- Messages
- 2,552
Welcome to the MrExcel Message Board!
We need VBA help to solve this problem. The code is supposed to check if the changed cell is D4, then check its value, and if it is 10000 then hide the rows, otherwise, show the rows.
Right click on the worksheet tab, and click on the View Code command. Find the Worksheet_Change event procedure and copy and paste the following code.
This is a very specific solution to show you how it would work according to the sample you provided. The "If" condition could be expanded by using multiple ElseIf statements for different values and cells, and it depends on your exact need.
Side note: If D4 is a formula cells instead of user entry, then this solution won't work. In that case, you can use the following version. I personally don't like using the Worksheet_Change event procedure like this as it will be called at each cell change on the worksheet), but we need an event trigger to check the cell value.
Another side note: If D4 formula depends on another worksheet cell value, then we'll need to use Thisworkbook class object's Workbook_SheetChange event procedure to be able to catch the value change in D4 based on the other worksheet cell changing. But it is another story and hopefully this is not the case. However, let us know if that's the case.
We need VBA help to solve this problem. The code is supposed to check if the changed cell is D4, then check its value, and if it is 10000 then hide the rows, otherwise, show the rows.
Right click on the worksheet tab, and click on the View Code command. Find the Worksheet_Change event procedure and copy and paste the following code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then
Rows("62:75").Hidden = Target.Value = 10000
End If
End SubThis is a very specific solution to show you how it would work according to the sample you provided. The "If" condition could be expanded by using multiple ElseIf statements for different values and cells, and it depends on your exact need.
Side note: If D4 is a formula cells instead of user entry, then this solution won't work. In that case, you can use the following version. I personally don't like using the Worksheet_Change event procedure like this as it will be called at each cell change on the worksheet), but we need an event trigger to check the cell value.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Rows("62:75").Hidden = Range("D4").Value = 10000
End SubAnother side note: If D4 formula depends on another worksheet cell value, then we'll need to use Thisworkbook class object's Workbook_SheetChange event procedure to be able to catch the value change in D4 based on the other worksheet cell changing. But it is another story and hopefully this is not the case. However, let us know if that's the case.
Upvote
0
