Request Assistance for debugging hide/unhide macro

[ganesh_s86]

I am trying to create a macro to automatically hide/unhide rows based on input given to cell c16. Unfortunately the code below seems to have no effect . I have used this code in the View code option when right clicking sheet tab.

Please help debug this.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = c16 Then
Select Case Target.Value
Case 1
Range("26:40").EntireRow.Hidden = True
If Target.Address = "$D$20" And Target.Value = "Release" Then
Range("23:24").EntireRow.Hidden = True
Else
Range("23:24").EntireRow.Hidden = False
End If

Case 2
Range("26:33").EntireRow.Hidden = False
Range("33:40").EntireRow.Hidden = True
If Target.Address = "$D$28" And Target.Value = "Release" Then
Range("31:32").EntireRow.Hidden = True
Else
Range("31:32").EntireRow.Hidden = False
End If

Case 3
Range("26:40").EntireRow.Hidden = False
If Target.Address = "$D$36" And Target.Value = "Release" Then
Range("39:40").EntireRow.Hidden = True
Else
Range("39:40").EntireRow.Hidden = False
End Select
End If
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C16" Then
    Select Case Target.Value
    Case 1
        Rows("26:40").Hidden = True
        If Target.Value = "Release" Then
            Rows("23:24").Hidden = True
        Else
            Rows("23:24").Hidden = False
        End If
    
    Case 2
        Rows("26:33").Hidden = False
        Rows("33:40").Hidden = True
        If Target.Value = "Release" Then
            Rows("31:32").Hidden = True
        Else
            Rows("31:32").Hidden = False
        End If
    
    Case 3
        Rows("26:40").Hidden = False
        If Target.Value = "Release" Then
            Rows("39:40").Hidden = True
        Else
            Rows("39:40").Hidden = False
        End If
    End Select
End If
End Sub

 

[Richard Schollar]

Hi

You test to see if the cell that is changed is C16 then enter the case statements if this is true. However, you also contain tests in each case statement to determine if Target is an alternative address (D20, D28, D36) - but if Target is C16 it isn't going to be any of these other cells is it?

It would probably help if you could explain in words what the conditions are that you want the hiding/unhiding to operate on. It isn't immediately obvious in the code because of the issue mentioned above.

 

[ganesh_s86]

Thanks for the quick replies. How ever the suggested changes do not seem to have any effect

Hi

You test to see if the cell that is changed is C16 then enter the case statements if this is true. However, you also contain tests in each case statement to determine if Target is an alternative address (D20, D28, D36) - but if Target is C16 it isn't going to be any of these other cells is it?

It would probably help if you could explain in words what the conditions are that you want the hiding/unhiding to operate on. It isn't immediately obvious in the code because of the issue mentioned above.

The idea is that if the cell C16 has value 1, then rows 26 to 40 are hidden. Further if the cell D20 has the value "release", then rows 23 & 24 are hidden.

Similarly when Cell C16 has value 2, rows 26 to 33 need to be hidden and so on.

I using 2 different tests, one for the overall case statement and one test within each case. I hope the idea behind the code is clearer.

 

[VoG]

Perhaps like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C16" Then
    Select Case Target.Value
    Case 1
        Rows("26:40").Hidden = True
        If Range("D20").Value = "Release" Then
            Rows("23:24").Hidden = True
        Else
            Rows("23:24").Hidden = False
        End If
    
    Case 2
        Rows("26:33").Hidden = False
        Rows("33:40").Hidden = True
        If Range("D28").Value = "Release" Then
            Rows("31:32").Hidden = True
        Else
            Rows("31:32").Hidden = False
        End If
    
    Case 3
        Rows("26:40").Hidden = False
        If Range("D36").Value = "Release" Then
            Rows("39:40").Hidden = True
        Else
            Rows("39:40").Hidden = False
        End If
    End Select
End If
End Sub

 

[ganesh_s86]

Thanks for you time!!
That seems to have solved the problem