I have written a code to auto hide/unhide rows however code is not working

[siddo]

can you help me figure out - something is wrong with the code and I'm unable to figure out. The code does not auto hide/unhide the rows

Private Sub Worksheet_Changes(ByVal Target1 As Range)

Application.ScreenUpdating = False
Application.DisplayAlerts = False


If Target1.Address = "$B$12" Then
    Dim b12val As String
    
Sheets("RawData").Rows.Hidden = False
    
b12val = Target1.Value
    
If b12val = 0 Then
        Sheets("RawData").Range("A13:B27").EntireRow.Hidden = True
   
ElseIf b12val = 1 Then
        Sheets("RawData").Range("A16:B27").EntireRow.Hidden = True
        
ElseIf b12val = 2 Then
        Sheets("RawData").Range("A19:B27").EntireRow.Hidden = True
        
ElseIf b12val = 3 Then
        Sheets("RawData").Range("A22:B27").EntireRow.Hidden = True
        
ElseIf b12val = 4 Then
        Sheets("RawData").Range("A25:B27").EntireRow.Hidden = True
        
ElseIf b12val = 5 Then
     Sheets("RawData").Rows.Hidden = False
    
End If
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[siddo]

PS : I already have one even code in the same sheet, is it possible to have 2 event code in the same sheet? if no, is there any workaround?

 

[johnnyL]

The following works for me:

Private Sub Worksheet_Change(ByVal Target1 As Range)
'
    If Target1.Address = "$B$12" Then
        Dim b12val As String
'
        Sheets("RawData").Rows.Hidden = False
'
        b12val = Target1.Value
'
        Select Case b12val
            Case Is = 0: Sheets("RawData").Range("A13:B27").EntireRow.Hidden = True
            Case Is = 1: Sheets("RawData").Range("A16:B27").EntireRow.Hidden = True
            Case Is = 2: Sheets("RawData").Range("A19:B27").EntireRow.Hidden = True
            Case Is = 3: Sheets("RawData").Range("A22:B27").EntireRow.Hidden = True
            Case Is = 4: Sheets("RawData").Range("A25:B27").EntireRow.Hidden = True
            Case Is = 5: Sheets("RawData").Rows.Hidden = False
        End Select
    End If
End Sub

 

[johnnyL]

PS : I already have one even code in the same sheet, is it possible to have 2 event code in the same sheet? if no, is there any workaround?

You can have more than one event code in the same sheet, but not with the same name. If you have more than one that require the same name then you have to combine the codes into one sub routine.

 

[johnnyL]

Glad to be of help @siddo.

 

[bebo021999]

Alternative code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Raw Data")
    .Rows.Hidden = False
    If Intersect(Target, .Range("B12")) Is Nothing Or Target.Value >= 5 Then Exit Sub
    .Range(.Cells(13 + Target.Value * 3, "A"), .Cells(27, "B")).EntireRow.Hidden = True
End With
End Sub

 

[johnnyL]

You really don't have any friends do you?

Just kidding, Nice solution! Other than the sheet name change.

 

[bebo021999]

You really don't have any friends do you?

Just kidding, Nice solution! Other than the sheet name change.

Haha, yes, sheet name changed.

 

[johnnyL]

Nice solution!