Hiding rows based on cell value

[Newbie415]

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).

 

[akmatz]

Sample data would be helpful.

 

[smozgur]

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.

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 Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)
    Rows("62:75").Hidden = Range("D4").Value = 10000
End Sub

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.

 

[ninjapaping]

what code to use if each rows are based on cell within the row? for example hide row62 if D62=0, otherwise show, then hide row63 if D63=0, otherwise show, and so on...