Auto Hide rows based on cell value

[ludlow]

I wrote the below script, but it is not working as I intended. I would like to view a certain range based on the text in cell A1. However, if i change the value more than once, a number of rows are hidden instead of the expected range. Any help would be greatly appreciated. Thanks.


Private Sub My_test_Case()
Select Case Range("$A$1").Text
Case Is = "tom"
Rows("3:4").Select
Case Is = "steve"
Rows("5:6").EntireRow.Hidden = True
Case Is = "tom"
Rows("7:8").EntireRow.Hidden = True
Case Is = "rich"
Rows("9:14").EntireRow.Hidden = True
Case Else
Rows("3:14").Select
Selection.EntireRow.Hidden = False

End Select
End Sub

 

[ChrisM]

You have to reset all rows to visible at the start of your code, otherwise you'll just keep hiding more and more rows every time the value in A1 changes.

Private Sub My_test_Case()
Rows("3:14").EntireRow.Hidden = False
Select Case Range("$A$1").Text
...
...

 

[ludlow]

You have to reset all rows to visible at the start of your code, otherwise you'll just keep hiding more and more rows every time the value in A1 changes.

Private Sub My_test_Case()
Rows("3:14").EntireRow.Hidden = False
Select Case Range("$A$1").Text
...
...

Chris,
Thank you very much. That solved my issue.

ludlow

 

[ludlow]

In building on top of the last issue, how do I auto run the script? I tried the below code, but with no avail.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Rows("3:15").EntireRow.Hidden = False

Select Case Range("$A$1").Text

Case Is = "Tom"
Rows("7:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Steve"
Range("3:6,10:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Rich"
Range("3:9,13:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Shelly"
Range("3:12").Select
Selection.EntireRow.Hidden = True

Case Else
Range("3:15").Select
Selection.EntireRow.Hidden = False

End Select

End Sub

 

[ChrisM]

This code should go in the worksheet module of the desired sheet you are working with:

Private Sub Worksheet_Calculate()

Rows("3:14").EntireRow.Hidden = False
Select Case Range("$A$1").Text
Case Is = "tom"
Rows("3:4").Select
Case Is = "steve"
Rows("5:6").EntireRow.Hidden = True
Case Is = "tom"
Rows("7:8").EntireRow.Hidden = True
Case Is = "rich"
Rows("9:14").EntireRow.Hidden = True
Case Else
Rows("3:14").Select
Selection.EntireRow.Hidden = False

End Select
End Sub

I'm not sure what the trigger for your code is suppose to be. This will run any time cells recalculate. You chould modify this to the "Change" event if you only want the code to run when the user types something new in A1.

 

[ludlow]

This code should go in the worksheet module of the desired sheet you are working with:

I'm not sure what the trigger for your code is suppose to be. This will run any time cells recalculate. You chould modify this to the "Change" event if you only want the code to run when the user types something new in A1.

I tried with the Change event, but no luck. The first time I tried the code, it worked. I have not been able to replicate it again.

Private Sub Worksheet_sheetChange(ByVal Target As Range)
Range("3:15").EntireRow.Hidden = False
Select Case Target.Address = ("$A$1")
Case Is = "Tom"
Range("7:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Steve"
Range("3:6,10:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Rich"
Range("3:9,13:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Shelly"
Range("3:12").Select
Selection.EntireRow.Hidden = True

Case Else
Range("3:15").Select
Selection.EntireRow.Hidden = False
End Select
End Sub

 

[ChrisM]

are you sure you placed the Change event code in the right worksheet code module?

 

[ludlow]

are you sure you placed the Change event code in the right worksheet code module?

I sure hope so. This is what I am doing.
1: Sheet1 - where the data are located
2: Sheet 2 & 3 - completely blank (untouched)
3: Sheet1 - Right click the excel symbol
- Select Sheet1 (Under Proj Explorer)
- Pasted the below script onto the form
- Press the RUN button --> Name the macro
- The macro names appears under module1
4: Change the value on Sheet1 - NOTHING occurs

Note: I noticed that the script moved from Sheet1 to This Workbook. With all that said and the comments made, I can only come to the conclusion...I am doing something majorly wrong.




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("3:15").EntireRow.Hidden = False
Select Case Target.Address = ("$A$1")
Case Is = "Tom"
Range("7:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Steve"
Range("3:6,10:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Rich"
Range("3:9,13:15").Select
Selection.EntireRow.Hidden = True

Case Is = "Shelly"
Range("3:12").Select
Selection.EntireRow.Hidden = True

Case Else
Range("3:15").Select
Selection.EntireRow.Hidden = False
End Select
End Sub

 

[ChrisM]

you were doing just fine up til this step: Press the run button. Not sure why you felt you had to do that.

All you need to do is open the VBE and paste the code into the desired worksheet module (like you did), close the VBE, and save your workbook normally.

 

[ludlow]

you were doing just fine up til this step: Press the run button. Not sure why you felt you had to do that.

All you need to do is open the VBE and paste the code into the desired worksheet module (like you did), close the VBE, and save your workbook normally.

I am running into hiccups between readying and trying to fill in the gaps of step by step process...but learning as I go - of which I am thankful for.

Nonetheless, I followed your advice and get the famous error - VBA: Run-time error 13, type mismatch</SPAN>?
</SPAN></SPAN>Any clues?