CODEWITHSUNDEEP
vbaexcelhide
Judit Casacuberta Bagó
Judit Casacuberta Bagó

Reputation: 21

Hide/Unhide some rows according a list value

I'm trying to hide/unhide some rows according a list value and if they are hidden set a cell to 0. The code is hidding the rows if I select 1 but it won't unhide them if I select any other value. The values I have available are 1 to 5.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20180822
    If Target.Address = "$F$16" Then
        If Target.Value <= 2 Then
            Application.Rows("22:25").Select
            Application.Selection.EntireRow.Hidden = True
            Range("F22").Value = "0"
            Range("F23").Value = "0"
            Range("F24").Value = "0"
            Range("F25").Value = "0"
        ElseIf Target.Value <= 3 Then
            Application.Rows("23:25").Select
            Application.Selection.EntireRow.Hidden = True
            Range("F23").Value = "0"
            Range("F24").Value = "0"
            Range("F25").Value = "0"
        ElseIf Target.Value <= 4 Then
            Application.Rows("24:25").Select
            Application.Selection.EntireRow.Hidden = True
            Range("F24").Value = "0"
            Range("F25").Value = "0"
        ElseIf Target.Value <= 5 Then
            Application.Rows("25:25").Select
            Application.Selection.EntireRow.Hidden = True
            Range("F25").Value = "0"
        ElseIf Target.Value <= 6 Then
            Application.Rows("22:25").Select
            Application.Selection.EntireRow.Hidden = False
        End If
    End If
End Sub

Upvotes: 0

Views: 69

Answers (2)

oxwilder
oxwilder

Reputation: 767

Your code only says to hide rows under a certain condition, it doesn't say to show them under others except where the cell = 6, but the logic would never get that far because any value from 1-5 would meet the first 5 conditions and then exit the if statement.

If you want to flip the hidden boolean (the true/false value) back and forth, you could dim a boolean variable as not itself and then set the hidden property to that value:

Dim TorF as Boolean
TorF = Application.Selection.EntireRow.Hidden 'to set the boolean's value

Application.Selection.EntireRow.Hidden = Not TorF 'to flip it

Upvotes: 0

Jefferson
Jefferson

Reputation: 26

By selecting 1, you are hitting the first IF statement and hiding 22:25. By trying to change the value, won't do anything because "ElseIf Target.Value <= 6 Then" will never get executed, because every value is <= 6. You need to "reset" your values when the event is fired -> add this to the top of the Sub prior to the If Thens...

If Target.Address = "$F$16" Then
        Application.Rows("22:25").Select
        Application.Selection.EntireRow.Hidden = False
        Range("F22").Value = "[whatever default value should be]"
        Range("F23").Value = "[whatever default value should be]"
        Range("F24").Value = "[whatever default value should be]"
        Range("F25").Value = "[whatever default value should be]"

Upvotes: 1

Related Questions