CODEWITHSUNDEEP
excelvba
user2654242
user2654242

Reputation:

Unhide rows in Excel with VBA

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.

Upvotes: 2

Views: 23616

Answers (1)

gembird
gembird

Reputation: 14053

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

Upvotes: 3

Related Questions