Hide Rows in Excel based on cell value

user_14997684 0
Viewed 464

I have a multiple selection, Option buttons, that change the value of cell D7 from 1 to 5, depending on choice. I want to unhide rows 16 to 26 if value is 1 and hide them if it's different, and so on for every other value.

But I can't even get this to work at all, and I'm not sure what I'm doing wrong.

Update: If I change the cell value, nothing happens, but if I delete all contents and add a value it gives: "Argument not optional", and it highlights this part of the code for me:

Private Sub Worksheet_Change(ByVal Target as Excel.Range)

Thank you

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If IsNumeric(Target) And Target.Address = "$D$7" Then
        Select Case Target.Value
            Case 0 To 90: Cell_Hider
        End Select
    End If
End Sub

Sub Cell_Hider(ByVal Target As Range)
    If Range("$D$7").Value = "1" Then
        Rows("16:26").EntireRow.Hidden = False
    Else
        Rows("16:26").EntireRow.Hidden = True
    End If
    
End Sub
vba excel
2 Answers
  1. Your procedure Cell_Hider needs an argument but your code calls it without argument Case 0 To 90: Cell_Hider
  2. You call Cell_Hider if the value is between 0 and 90 then that procedure needs the value to be 1 to show the rows and 0 or 2 to 90 will hide them. If you put 100 in that cell nothing happens at all. Sounds not like what you expect to me.
  3. "1" is text not a number!

Something like the following would work:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If IsNumeric(Target) And Target.Address = "$D$7" Then
        Select Case Target.Value
            Case 0 To 90: Cell_Hider Target
        End Select
    End If
End Sub

Sub Cell_Hider(ByVal Target As Range)
    If Target.Value = 1 Then
        Target.Parent.Rows("16:26").EntireRow.Hidden = False
    Else
        Target.Parent.Rows("16:26").EntireRow.Hidden = True
    End If
End Sub

Even though it doesn't look logic to me and I'm not sure what you are exactly trying to achieve.

Note that you can shorten it to

Sub Cell_Hider(ByVal Target As Range)
    Target.Parent.Rows("16:26").EntireRow.Hidden = Not Target.Value = 1
End Sub
user_32196130

Hide/Unhide Rows on Cell Change

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim sCell As Range: Set sCell = Me.Range("D7")
    
    If Intersect(sCell, Target) Is Nothing Then Exit Sub
    
    If IsNumeric(sCell.Value) Then
        HideRows sCell
    End If

End Sub

Sub HideRows(ByVal SourceCell As Range)
    If SourceCell.Value = 1 Then
        SourceCell.Worksheet.Rows("16:26").Hidden = False
    Else
        SourceCell.Worksheet.Rows("16:26").Hidden = True
    End If
End Sub
user_98140690
Related