2

I'm trying to use the following code in Excel, but it is not working. Cell AE25 refers to a cell using data validation to pull the numbers 1-8 from a list.

Select Case Range("AE25").Value
   Case 1
      Range("A26:A28").EntireRow.Hidden = False
   Case 2
      Range("A26:A29").EntireRow.Hidden = False
   Case 3
      Range("A26:A30").EntireRow.Hidden = False
   Case 4
      Range("A26:A31").EntireRow.Hidden = False
   Case 5
      Range("A26:A32").EntireRow.Hidden = False
   Case 6
      Range("A26:A33").EntireRow.Hidden = False
   Case 7
      Range("A26:A34").EntireRow.Hidden = False
   Case 8
      Range("A26:A35").EntireRow.Hidden = False
End Select

Any help would be appreciated.

Improve this question
7
  • any error? which and in which line... Commented Aug 6, 2013 at 8:06
  • No errors, just not running - I am a VBA novice, so there is a possibility I've done something really fundamental wrong. Commented Aug 6, 2013 at 8:07
  • run your code with F8 key and check the way compilation goes through. This should help... Commented Aug 6, 2013 at 8:11
  • Where should this code be pasted? Do I just right click the sheet and click View Code and put it in there? Commented Aug 6, 2013 at 8:15
  • it depends on the way you want to start it. Commented Aug 6, 2013 at 8:18

1 Answer 1

Reset to default
3

Paste this code into module of the sheet where your data are, e.g. you have data in 'Sheet1', then paste it to 'Sheet1' class module. HTH

Private Sub Worksheet_Change(ByVal Target As Range)
    ' AE25
    If (Not Intersect(Target, Range("AE25")) Is Nothing) Then
        Select Case Target.Value
            Case 1
                Range("A26:A28").EntireRow.Hidden = False
            Case 2
                Range("A26:A29").EntireRow.Hidden = False
            Case 3
                Range("A26:A30").EntireRow.Hidden = False
            Case 4
                Range("A26:A31").EntireRow.Hidden = False
            Case 5
                Range("A26:A32").EntireRow.Hidden = False
            Case 6
                Range("A26:A33").EntireRow.Hidden = False
            Case 7
                Range("A26:A34").EntireRow.Hidden = False
            Case 8
                Range("A26:A35").EntireRow.Hidden = False
            Case Else
                ' hide all rows 26-35 if value is not equal to 1-8
                Range("A26:A35").EntireRow.Hidden = True
        End Select
    End If

    ' Z40
    If (Not Intersect(Target, Range("Z40")) Is Nothing) Then
        Select Case Target.Value
            Case "PowerPoint", "Verbal"
                Range("A41").EntireRow.Hidden = False
            Case "None"
                Range("A41").EntireRow.Hidden = True
            Case Else

        End Select
    End If
End Sub

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Altered to make the range-change check more robust - as originally supplied, if AE25 changed in some way with other cells (unlikely I know) then the .Address property wouldn't match the string used.
That works great. I've also added some extra lines to 're-hide' rows in case AE25 is selected as a high number, and then updated to a lower number. How would I re-hide all rows 26-35 if AE25 is not equal to 1-8?
Also, what about if I am using text in the input cell? I have another row to hide which is depending on Z40, if the text is "PowerPoint" or "Verbal", it should unhide row 41. If the text is "None", it should hide row 41. Row 41 will start hidden.
@DanielDusek That's great! One final thing though, if the value in AE25 or Z40 is deleted, it brings up a run time error, and I want it to hide the rows 26:35 or 41. Can you add a line for this?
@KirtjE Can't reproduce this error, can you be more specific? On which line and what eroor exactly it is and what action are you doing before the error pops up?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.