Hide Rows Based on Cell Value (formula)

[hdtech96]

I have a simple routine built that hides some rows based on the value of a cell. Note the code below.
(I should add that I am not a programmer so this is code I have kluged together from other examples)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$U$13" Then

If Target.Value = 1 Then
Rows("65:77").EntireRow.Hidden = True
Else
If Target.Value = 2 Then
Rows("65:77").EntireRow.Hidden = False
End If
End If
End If
End Sub

My problem comes with the cell U13 itself. If I manually enter 1 or 2 into that cell, everything works great. If I change U13 to be a formula (IF) that results only in 1 or 2, the code no longer works. I am not sure if it matters but the IF formula results are dependent on results from another sheet.

I was worried that it was a text vs number kind of thing so I added quotes around the 1 or 2 in my code and that didn't matter. I tried reading U13 into string variable and going about that way but still no luck.

I now understand that Worksheet_Change is not what I want because a formula update is not considered a change. I think I need worksheet_calculate? So I made this version of code:

Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

Rows("1:" & Worksheets("Case").UsedRange.Rows.Count).EntireRow.Hidden = False

MyResult = Worksheets("Case").Cells(21, 13).Value

Select Case MyResult
Case "1"
Rows("65:77").EntireRow.Hidden = True
Case "2"
Rows("65:77").EntireRow.Hidden = False
End Select

Application.EnableEvents = True
End Sub

This doesnt appear to work either! :dunno

I have attached a trimmed down version of the file. Note that there are many more tabs so while S13 can be changed in this version, S13 is linked to another sheet in the real version. I just made U13 as a placeholder to reference.

Thanks for any help!

[Bob Phillips]

This works fine for me



Private Sub Worksheet_Calculate()

Application.EnableEvents = False

Me.Rows("1:" & Worksheets("Case").UsedRange.Rows.Count).EntireRow.Hidden = False

With Me.Rows("65:77")

Select Case Worksheets("Case").Range("U13").Value
Case 1: .EntireRow.Hidden = True
Case 2: .EntireRow.Hidden = False
End Select
End With

Application.EnableEvents = True
End Sub


But why not just use a Change event on cell T13?

[hdtech96]

Thanks xld!
It works for me too in my example... just need to port it over to confirm. I cant do a change event because in the real sheet, S13:T14 is a formula as well. I move the drop down validation into the same sheet for the example. I really needed to know how to trigger based on a formula change instead of a cell change.

Just to help expand my vba knowledge, is Me.Rows a variable or a command?

[Bob Phillips]

No, Me.Rows is the same as Worksheet("Case").Rows. Me refers to the containing object (worksheet here).

[hdtech96]

It works!!! Many many many thanks!

[Chabu]

If the data you are filtering is part of a data range then you are better off iif you use the autofilter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$13" Then
Dim sCase As Worksheet
Set sCase = Worksheets("Case")
Dim r As Range
Set r = sCase.Cells
Select Case Target.Value
Case Is = 1
sCase.Cells.AutoFilter field:=5
Case Is = 2
sCase.Cells.AutoFilter field:=5, Criteria1:="x"
End Select
End If
End Sub

This will filter the list if the value in column E is x if the control field (U13) has the value 2. It will reset the filter if the value =1

Of course this might not be what you tried to do but if you did it is always better to use excel features in your code instead of trying to reproduce them.

Greetings
ChaBu